Join two tables based on multiple conditions and priority SQL

Question

I'm trying to join two tables together that would return a classification of a region based on the city and postcode.

TABLE A contains information about the customer and their location

TABLE B contains information about suburbs, postcodes and regional classifications

I would like to match the two tables in order of preference:

1. Suburb and Postcode
2. Postcode
3. Suburb

I'm having a difficult time joining the two tables. I have this code and so far, it only matches the first preference. The classification returns a NULL if the postcode OR suburb is NULL.

SELECT 
A.*, B.Classification 
INTO NEWTABLE 
FROM TABLEA AS A 
   LEFT JOIN TABLEB AS B
      ON A.City = B.City
      AND A.Postcode = B.Postcode

I feel the solution is quite simple but I just can't get my head around the SQL language. I'm using Microsoft SQL. Thank you so much!

Answer 1

This is a great place to use lateral joins -- OUTER APPLY, in this case:

SELECT A.*, B.Classification 
INTO NEWTABLE 
FROM TABLEA A OUTER APPLY
     (SELECT TOP (1) B.*
      FROM ((SELECT B.*
             FROM TABLEB B
             WHERE A.City = B.City 
            ) UNION ALL
            (SELECT B.*
             FROM TABLEB B
             WHERE A.Postcode = B.Postcode AND (A.City <> B.City OR B.City IS NULL)
            )
           ) B
      ORDER BY ((CASE WHEN B.PostCode = A.PostCode THEN 2 ELSE 0 END) +
                (CASE WHEN B.City = A.PostCode THEN 1 ELSE 0 END)
               ) DESC
     );

This is structured so the query can take advantage of indexes on B(Postcode) and B(City).

Assuming that the match by Postcode and City means that the other column is NULL, then you can also do this with LEFT JOINs:

SELECT A.*,
       COALESCE(BCP.Classification, BP.Classification, BC.Classification) as Classification
INTO NEWTABLE 
FROM TABLEA AS A LEFT JOIN
     TABLEB  BCP
     ON BCP.City = A.City AND
        BCP.Postcode = A.Postcode LEFT JOIN
     TABLEB BP
     ON BP.Postcode = A.Postcode AND
        BP.City IS NULL AND
        BCP.City IS NULL LEFT JOIN  -- no match above
     TABLEB BC
     ON BC.City = A.City AND
        BC.PostCode IS NULL AND
        BP.PostCode IS NULL;

This is carefully structured to avoid duplicate rows.

Answer 2

One way of doing it is to LEFT JOIN the same table 3 times and use a COALESCE:

SELECT A.*, COALESCE(B1.Classification,B2.Classification,B3.Classification) as Classification
INTO NEWTABLE 
FROM TABLEA AS A 
LEFT JOIN TABLEB AS B1
      ON A.City = B1.City
      AND A.Postcode = B1.Postcode
LEFT JOIN TABLEB AS B2
      ON A.Postcode = B2.Postcode
LEFT JOIN TABLEB AS B3
      ON A.City = B3.City