CODEWITHSUNDEEP
sqlms-access
Sonicsmooth
Sonicsmooth

Reputation: 2767

Left join and use left fields if left fields are empty, otherwise use right fields

Using Access driver from C#.

I'm trying to left join, with fields from right overriding left's fields, but only if right fields exist. So far I have this, which always overrides left's field because Col1 and Col2 from left are ignored:

SELECT LeftAlias.PartNumber AS PartNumber, Col1, Col2, Col3
INTO TableA FROM 
(SELECT PartNumber, Col3 FROM TableA IN 'LeftFile.mdb') AS LeftAlias
LEFT JOIN
(SELECT PartNumber, Col1, Col2 FROM TableA IN 'RightFile.mdb') AS RightAlias
ON LeftAlias.PartNumber=RightAlias.PartNumber
ORDER BY LeftAlias.PartNumber;


LeftFile.TableA
PartNumber | Col1   | Col2  | Col3
-----------+--------+-------+------
123-abc    | cat    | dog   | 10
456-def    | horse  | zebra | 11
789-ghi    | ameoba | mouse | 12

RightFile.TableA
PartNumber | Col1   | Col2
-----------+--------+-----
123-abc    | lion   | hyena
456-def    | <null> | <null>

Desired output TableA
PartNumber | Col1   | Col2  | Col3
-----------+--------+-------+-------
123-abc    | lion   | hyena | 10
456-def    | horse  | zebra | 11
789-ghi    | amoeba | mouse | 12

How do I get the override only if the field exists in RightFile? The PartNumber I'm joining on will never be NULL.

Upvotes: 0

Views: 35

Answers (1)

Josh Part
Josh Part

Reputation: 2164

Your query should like this:

SELECT LeftAlias.PartNumber AS PartNumber, 
       IIF(ISNULL(RightAlias.Col1), LeftAlias.Col1, RightAlias.Col1) AS Col1, 
       IIF(ISNULL(RightAlias.Col2), LeftAlias.Col2, RightAlias.Col2) AS Col2, 
       LeftAlias.Col3
INTO TableA FROM 
(SELECT PartNumber, Col1, Col2, Col3 FROM TableA IN 'LeftFile.mdb') AS LeftAlias
LEFT JOIN
(SELECT PartNumber, Col1, Col2 FROM TableA IN 'RightFile.mdb') AS RightAlias
ON LeftAlias.PartNumber=RightAlias.PartNumber
ORDER BY LeftAlias.PartNumber;

IIF function evaluates the first parameter as an expression, and returns the second parameter if the expression is true, and the third parameter if the expression is false. ISNULL returns true or false depending if the value passed as parameter is null or not.

Note that, if using Access 2007 or above, you can shorten these lines:

IIF(ISNULL(RightAlias.Col1), LeftAlias.Col1, RightAlias.Col1) AS Col1, 
IIF(ISNULL(RightAlias.Col2), LeftAlias.Col2, RightAlias.Col2) AS Col2,

With the Nz function:

Nz(RightAlias.Col1, LeftAlias.Col1) AS Col1, 
Nz(RightAlias.Col2, LeftAlias.Col2) AS Col2,

Upvotes: 1

Related Questions