Conditional join based on lookup

Question

Apologies if a similar problem is posted earlier, I couldn't find the same.

Problem: I need to join two tables based a conditional look up in the second table.

Tables: Below are the two tables which have a subset of the total fields.

+-------------------------------------------------------+
|                           Persons                     |
+----------+------------+---------------+---------------+
| PersonID | PersonName | HomeAddressID | WorkAddressID |
+----------+------------+---------------+---------------+
| P1       | Doe, John  | HA1           | WA1           |
+----------+------------+---------------+---------------+
| P2       | Doe, Jane  | HA2           | WA2           |
+----------+------------+---------------+---------------+
| P3       | Doe, Jane  |               | WA3           |
+----------+------------+---------------+---------------+


+-----------------------------------+
|             Addresses             |
+-----------+--------+------+-------+
| AddressID | Street | City | State |
+-----------+--------+------+-------+
| HA1       | 123    | A    | B     |
+-----------+--------+------+-------+
| WA1       | 456    | C    | D     |
+-----------+--------+------+-------+
| HA2       | 111    |      |       |
+-----------+--------+------+-------+
| WA2       | 101    | G    | H     |
+-----------+--------+------+-------+
| WA3       | 333    | I    | J     |
+-----------+--------+------+-------+

Current Scenario: The SELECT query in a view fetches PersonName from first table and work address fields from second table. (Join is on WorkAddressID)

Expected Result: The SELECT query should fetch PersonName field from first table and address fields from second table conditions being:

If state for home address is available then display Street, City and State for home address.
If state for home address is NULL/blank then display Street, City and State for work address.

Notes:

Many rows in Persons table do not have HomeAddressID but all do have WorkAddressID.
Many rows in Addresses table do not have City and State information for Home addresses.
While this may look like a design flaw, I'm not in a position to re-engineer the database as there are hundreds of objects and sub-objects depending on the original view.
There are 3 million+ rows in the Persons table so performance needs to be acceptable.
The current query has joins to at least 5 other views.

Please advise as to how I can address this problem.

Many thanks,

-V

What database are you using? MySQL, SQL-Server, Oracle, etc.? — Barmar, Apr 10 '14 at 22:51
Hi @paqogomez, I believe it should, I'll check and update in 24 hours. — Vishal, Apr 11 '14 at 05:14
I haven't had a chance to test this but I think it will work, marked your reply as answer, thanks a lot! :-) — Vishal, Apr 13 '14 at 21:07

score 3 · Answer 1 · answered Apr 10 '14 at 22:55

Here's a MySQL solution:

SELECT PersonName, 
       IF(h.State = '' OR h.State IS NULL, w.Street, h.Street) AS Street,
       IF(h.State = '' OR h.State IS NULL, w.City, h.City) AS City,
       IF(h.State = '' OR h.State IS NULL, w.State, h.State) AS State
FROM Persons AS p
JOIN Addresses AS w ON w.AddressID = p.WorkAddressID
LEFT JOIN Addresses as h ON h.AddressID = p.HomeAddressID

score 2 · Accepted Answer · answered Apr 10 '14 at 22:56

A self join would handle this:

select
    p.personname,
    case when ha.state is null then wa.street else ha.street end as street,
    case when ha.state is null then wa.city else ha.city end as city,
    case when ha.state is null then wa.state else ha.state end as state
from
    Persons p
    inner join addresses wa on p.workaddressid = wa.addressid
    left join addresses ha on p.homeaddressid = ha.addressid

This syntax would be for MSSQL

Edit: changed the home to a left join because of the criterion Many rows in Persons table do not have HomeAddressID

Conditional join based on lookup

2 Answers2