Tables Join to show nulls with no data 1

[NHW]

Hi all. I'm having problems doing a simple join (I think).. hope can get help here. I have 2 tables (due to other reasons I can't put the data into one single table to begin with):

Table A:
ID Year ValueA
-- ---- ------
1 2004 A
2 2004 B
2 2005 C
3 2005 D

Table B:
ID Year ValueB
-- ---- ------
2 2005 X
3 2005 Y
4 2005 Z

And I would like to return a recordset that shows something like this ("Year" is always queried on 2004 and 2005)

ID Year ValueA Value B
-- ---- ------ -------
1 2004 A (NULL)
2 2004 B (NULL)
2 2005 C X
3 2005 D Y
4 2005 (NULL) Z


Thanks in advance!

 

[unclejimbob]

This is my first attempt at this so there are probably better answers but this works for me:

select TableA.[ID], TableA.[Year], TableA.ValueA, TableB.ValueB from TableA left outer join TableB on
TableA.[ID] = TableB.[ID] and TableA.[Year] = TableB.[Year]
union
select TableB.[ID], TableB.[Year], TableA.ValueA, TableB.ValueB from TableB left outer join TableA on
TableA.[ID] = TableB.[ID] and TableA.[Year] = TableB.[Year]

 

[unclejimbob]

Or this...

select coalesce(TableA.[ID],TableB.[ID]) [ID],
coalesce(TableA.[Year],TableB.[Year]) [Year] ,TableA.ValueA, TableB.ValueB
from TableA full outer join TableB
on TableA.[ID]= TableB.[ID] and TableA.[Year] = TableB.[Year]

I only have SQL Server 2000 at home otherwise I'd look at using interesct under SQLS 2005 - this looks pretty ugly to me, anyone got a more elegant solution ?

 

[Walid]

SELECT A.KeyID, A.YearField, A.ValueA, B.ValueB
FROM A
LEFT JOIN B
ON A.KeyID = B.KeyID AND A.YearField = B.YearField
UNION ALL
SELECT B.KeyID, B.YearField, NULL, B.ValueB
FROM B
LEFT JOIN A
ON A.KeyID = B.KeyID AND A.YearField = B.YearField
WHERE A.YearField IS NULL

Personaly, I like the COALESCE solution.

Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[r937]

unclejimbob, that FULL OUTER JOIN with COALESCE is beautiful



r937.com | rudy.ca

 

[NHW]

Thanks so much! The coalesce function works like a charm. But could someone please kindly explain why it works this way? I checked books online and it doesn't seem to say much.. Thanks again!

NHW

 

[r937]

COALESCE takes any number of arguments, and returns the first non-null value going left to right

so in coalesce(TableA.[ID],TableB.[ID]) if TableA.[ID] is not null, then COALESCE returns that, no matter what TableB.[ID] is, but if TableA.[ID] is null, then it returns TableB.[ID], no matter what it is

if they were both null, of course, COALESCE would return null, but in a FULL OUTER JOIN, that'll never happen

and if they were both not null, then returning TableA.[ID] is correct since it will be the same as TableB.[ID], right?



r937.com | rudy.ca