join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join problem

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-06-26 : 08:38:36

Hi All,
I have 2 tables in the foll format:
TABLE 1 (table name)
col1 col2 col3 col4 (column name)
1 1 2 date-1
2 1 3 date-2

TABLE A
cola colb colc cold
1 1 2 date-3
3 1 4 date-4

i need to join both table 1 and table A in such a way that whatever data is common in both tables is picked ONLY from table 1 (since that has the latest data)
and whatever data is present in table A (minus common data) is also picked up.

Result expected:

1 1 2 date-1
2 1 3 date-2
3 1 4 date-4
Please suggest me how to get this. Thanks :)
P.S. i used a full outer join but that returns null values for many rows. inner join returns common data.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 08:48:40

[code]DECLARE @table1 TABLE
(
col1 int,
col2 int,
col3 int,
col4 varchar(10)
)
INSERT INTO @table1 (col1, col2, col3, col4)
SELECT 1, 1, 2, 'date-1' UNION ALL
SELECT 2, 1, 3, 'date-2'

DECLARE @tablea TABLE
(
cola int,
colb int,
colc int,
cold varchar(10)
)
INSERT INTO @tablea (cola, colb, colc, cold)
SELECT 1, 1, 2, 'date-3' UNION ALL
SELECT 3, 1, 4, 'date-4'

SELECT col1 = coalesce(t1.col1, ta.cola),
col2 = coalesce(t1.col2, ta.colb),
col3 = coalesce(t1.col3, ta.colc),
col4 = coalesce(t1.col4, ta.cold)
FROM @table1 t1
FULL OUTER JOIN @tablea ta
ON t1.col1 = ta.cola
AND t1.col2 = ta.colb
AND t1.col3 = ta.colc
/*
col1 col2 col3 col4
----------- ----------- ----------- ----------
1 1 2 date-1
2 1 3 date-2
3 1 4 date-4

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources