Outer Join woes

/*
Hi -
Is there a better way to get the desired results?
Thanks,
John
-- contents T1
C1 C2 A
-- -- --
1 1 100
1 2 100
-- contents T2
C1 C2 B
-- -- --
1 1 500
1 3 500
-- desired results
C1 C2 A B
-- -- -- --
1 1 100 500
1 2 100 NULL
1 3 NULL 500
*/
SET NOCOUNT ON
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
CREATE TABLE T1 (C1 int, C2 int, A int)
CREATE TABLE T2 (C1 int, C2 int, B int)
INSERT T1 VALUES (1,1, 100)
INSERT T1 VALUES (1, 2, 100)
INSERT T2 VALUES (1,1, 500)
INSERT T2 VALUES (1, 3, 500)
SELECT * FROM T1
SELECT * FROM T2
-- there's gotta be a better way!
SELECT CASE WHEN T1.C1 IS NULL THEN T2.C1 ELSE T1.C1 END 'C1',
CASE WHEN T1.C2 IS NULL THEN T2.C2 ELSE T1.C2 END 'C2',
T1.A,
T2.B
FROM T1
FULL OUTER JOIN t2
ON T1.C1 = T2.C1 AND T1.C2 = T2.C2
ORDER BY 1,2
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
SET NOCOUNT OFFYou can use function COALESCE to make it more readable.
SELECT
coalesce(T1.C1, T2.C1) as 'C1',
coalesce(T1.C2, T2.C2) as 'C2',
T1.A,
T2.B
FROM
T1
FULL OUTER JOIN t2
ON T1.C1 = T2.C1 AND T1.C2 = T2.C2
ORDER BY
C1,
C2
AMB
"John Beatty" wrote:

> /*
> Hi -
> Is there a better way to get the desired results?
> Thanks,
> John
> -- contents T1
> C1 C2 A
> -- -- --
> 1 1 100
> 1 2 100
> -- contents T2
> C1 C2 B
> -- -- --
> 1 1 500
> 1 3 500
> -- desired results
> C1 C2 A B
> -- -- -- --
> 1 1 100 500
> 1 2 100 NULL
> 1 3 NULL 500
> */
> SET NOCOUNT ON
> IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
> IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
> CREATE TABLE T1 (C1 int, C2 int, A int)
> CREATE TABLE T2 (C1 int, C2 int, B int)
> INSERT T1 VALUES (1,1, 100)
> INSERT T1 VALUES (1, 2, 100)
> INSERT T2 VALUES (1,1, 500)
> INSERT T2 VALUES (1, 3, 500)
> SELECT * FROM T1
> SELECT * FROM T2
> -- there's gotta be a better way!
> SELECT CASE WHEN T1.C1 IS NULL THEN T2.C1 ELSE T1.C1 END 'C1',
> CASE WHEN T1.C2 IS NULL THEN T2.C2 ELSE T1.C2 END 'C2',
> T1.A,
> T2.B
> FROM T1
> FULL OUTER JOIN t2
> ON T1.C1 = T2.C1 AND T1.C2 = T2.C2
> ORDER BY 1,2
> IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
> IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
> SET NOCOUNT OFF
>
>