CODEWITHSUNDEEP
sqlif-statementms-access-2007left-join
Oedhel
Oedhel

Reputation: 15

When field "IS NULL" join on an alternative field - Access 2007

So the short end of it is this is what I want to do, but I don't know the proper syntax.

Table 1, Table 2 Name, SSN, DOB, List, Date

I want to do a left join using the SSN, but when the SSN field IS NULL I want it to join on the DOB field Where the Name matches.

I can't join on name due to the file being 19k records and most of them are common names.

Upvotes: 1

Views: 1895

Answers (3)

ErikE
ErikE

Reputation: 50211

I believe that you can do what you want with a simple LEFT JOIN and the correct JOIN conditions. The trick in Access is that IT DOES NOT LIKE left joins with too few parentheses. When in doubt, add more parentheses. You will be surprised at what it makes possible in Access.

SELECT
   T1.*,
   T2.*
FROM
   (Table1 AS T1
   LEFT JOIN Table2 AS T2 ON (
      (T1.SSN = T2.SSN)
      OR (
         (T1.SSN IS NULL)
         AND (T1.DOB = T2.DOB)
         AND (T1.Name = T2.Name)
      )
   ));

If this doesn't work, please let me know and I'll see what I can do.

Note that you may or may not be able to go to the design view of this in the GUI query editor.

Also note that since NULL does not equal NULL, you don't need additional conditions before the OR about X.SSN IS NOT NULL.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91356

Perhaps:

SELECT  *
FROM Table7
INNER JOIN Table8
ON Table7.SSN = Table8.SSN
WHERE Table8.SSN Is Not Null
UNION
SELECT  *
FROM Table7
INNER JOIN Table8
ON Table7.DOB = Table8.DOB
WHERE Table8.SSN Is Null

Note that the result will not be editable.

Upvotes: 2

Angst
Angst

Reputation: 304

break it up into 2 queries

/1st query/ SELECT

LEFT JOIN ... ...WHERE Table2.SSN is NOT NULL

UNION /* 2nd query */ SELECT ..... FROM TABLE1 t1, TABLE2 t2 WHERE t2.SSN is NULL

Upvotes: 1

Related Questions