I am trying to pull addresses based on their type from certain accounts. The relation between the two tables is not based off account type but rather a customerId (a customer can have many different accounts whose accounts have their own separate ID’s). The customers account have a profile number and parent profile number either of which is linked to the profile number in the address table. So, what I am trying to do is determine if the account profile number is in the address table (as fintProfileNumber) and if it is list the address if it is a mailing address, if not then check if the account parent profile number is equal to the profile number in address table (as fintProfileNumber) and list that address if it is a mailing address. If none of these are present then use the default mailing address in the account table. I have attached an example of the tables.

https://sqlfiddle.com/sql-server/online-compiler?id=cce1f07c-77bd-4055-b214-77d2f3cf3529&id=4a505279-7d00-4a8d-91d1-aab6fbb4aa58&id=4a505279-7d00-4a8d-91d1-aab6fbb4aa58

I have tried an outer apply with the two tables and using an or statement in the where clause checking for profile number or parent profile number in the address file like so :

https://sqlfiddle.com/sql-server/online-compiler?id=c6fb4ef2-d29b-4b61-8603-d75b33dbad01&id=4a505279-7d00-4a8d-91d1-aab6fbb4aa58&id=4a505279-7d00-4a8d-91d1-aab6fbb4aa58

But the results are not what I expect. Accounts with the profile number in address table are correct but if it uses the parent profile number or none at all those are incorrect and use an address type I do not desire.