I have two tables, and I want ALL the data from both. If the tables have a matching AssetID, then join them on one row. If not, then on separate rows. A full outer join sounds like the right approach but I have a problem in how to select the keys depending on which table it comes from.
TABLE A TABLE B
AssetID | Valuable AssetID | Protected
------------------- -------------------
123 | Yes 123 | Yes
456 | No 321 | No
653 | Yes
Goal:
TABLE C
AssetID | Valuable | Protected
---------------------------
123 | Yes |Yes
456 | No |
653 | Yes |
321 | |No
SELECT TableA.AssetID, TableA.Valuable, TableB.Protected
FROM (
SELECT AssetID, Valuable
FROM TableA
) ta
FULL OUTER JOIN (
SELECT AssetID, Protected
FROM TableB
) tb ON ta.AssetID=tb.AssetID
Produces
TABLE C
AssetID | Valuable | Protected
---------------------------
123 | Yes |Yes
456 | No |
653 | Yes |
| |No <<<< PROBLEM
---------------------------
and I'm missing the key
You can use coalesce
to take the non-null assetID
from whatever table has it:
SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM
(
SELECT
AssetID,
Valuable
FROM
TableA
) ta
FULL OUTER JOIN
(SELECT
AssetID,
Protected
FROM
TableB
) tb
ON ta.AssetID=tb.AssetID
Note: You probably don't need the sub-queries, though, and omitting them can simplify the query considerably:
SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM TableA
FULL OUTER JOIN TableB
ON TableA.AssetID=TableB.AssetID