I'm working with a total of 4 tables here:

Product Table:
product\_id, product\_desc

Data 1
product\_id, location\_id, data1

Data 2
product\_id, location\_id, data2

Data 3
product\_id, location\_id, data3

The problem I'm having is not every product\_id or location\_id is included in each of the data tables. The query I have manages to pull the data correctly for the first data table, but not in instances where the product\_id or location\_id doesn't exist in data1, but does in data2. I understand this is due to the on condition going back to data1, but I'm not sure how to get the location\_nbr in there otherwise. I do have another table that lists all of the location numbers, but these do not correlate anywhere to the item table, so I have no way to join this.

```
select 
    item1.*
    ,coalesce(data1.location_id, data2.location_id, data3.location_id) as location_id
    ,data1.data1
    ,data2.data2
    ,data3.data3

from item1
    LEFT OUTER JOIN data1 AS data1
    ON data1.product_id = item1.ksn_id

    LEFT OUTER JOIN data2 AS data2
    ON data2.product_id = item1.ksn_id
    and data2.location_nbr = data1.location_nbr

    LEFT OUTER JOIN data3 AS data3
    ON data3.product_id = item1.ksn_id
    and data3.locn_nbr = data1.location_nbr

```