Full outer join on 3 tables. How to?

[Robert]

Hi,

I have 3 tables:
1) table a:
id int
colA int

2) table b:
id int
colB int

3) table c:
id int
colC int

If I'm given an id (e.g. @id = 2), how do I query the three tables for rows
that match @id? I need a full outer join. For example, if table c is the
only table containing a row matching @id the return values (colA, colB,
colC) sould be: NULL, NULL, n

Thanks.

[Plamen Ratchev]

Try this:

SELECT colA, colB, colC
FROM (
SELECT COALESCE(a.id, b.id) AS id, colA, colB
FROM a
FULL OUTER JOIN b
ON a.id = b.id) AS x
FULL OUTER JOIN c
ON c.id = x.id
WHERE x.id = @id
OR c.id = @id;

--
Plamen Ratchev
http://www.SQLStudio.com

[Jay Konigsberg]

Or this:

USE Work;

IF OBJECT_ID('dbo.tabA', 'U') IS NOT NULL DROP TABLE dbo.tabA;

IF OBJECT_ID('dbo.tabB', 'U') IS NOT NULL DROP TABLE dbo.tabB;

IF OBJECT_ID('dbo.tabC', 'U') IS NOT NULL DROP TABLE dbo.tabC;

CREATE TABLE dbo.tabA (id int, ColA int);

CREATE TABLE dbo.tabB (id int, ColB int);

CREATE TABLE dbo.tabC (id int, ColC int);

GO

INSERT INTO dbo.tabA (id) VALUES(0);

INSERT INTO dbo.tabA (id) VALUES(1);

INSERT INTO dbo.tabB (id) VALUES(0);

INSERT INTO dbo.tabC (id) VALUES(2);

INSERT INTO dbo.tabB (id) VALUES(3);

SELECT a.id a_id, b.id b_id, c.id c_id

FROM dbo.tabA a

FULL OUTER JOIN

(

SELECT id

FROM dbo.tabB

) b

ON a.id = b.id

FULL OUTER JOIN

(

SELECT id

FROM dbo.tabC

) c

ON a.id = c.id

;

--
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg

"Robert" <b...@robert.com> wrote in message
news:eJBPrY5v...@TK2MSFTNGP04.phx.gbl...

[--CELKO--]

None of these skeletons are tables; no keys and all columns are NULL-
able. You never said how you wanted to handle NULLs occurring in the
data versus generated NULLs. If you had bothered with real DDL, did
you mean to post this?

CREATE TABLE Alpha
(some_id INTEGER NOT NULL PRIMARY KEY,
col_a INTEGER NOT NULL);

CREATE TABLE Beta
(some_id INTEGER NOT NULL PRIMARY KEY,
col_b INTEGER NOT NULL);

CREATE TABLE Gamma
(some_id INTEGER NOT NULL PRIMARY KEY,
col_c INTEGER NOT NULL);

Assuming that some_id is a key,you can use:

SELECT X.some_id, MAX(X.col_a) AS col_a, MAX(X.col_b) AS col_b,
MAX(X.col_c) AS col_c
FROM (SELECT some_id, col_a, NULL, NULL FROM Alpha WHERE some_id =
@in_some_id
UNION ALL
SELECT some_id, NULL, col_b, NULL FROM Beta WHERE some_id =
@in_some_id
UNION ALL
SELECT some_id, col_c, NULL, NULL FROM Gamma WHERE some_id =
@in_some_id)
AS X(col_a, col_b, col_c)
GROUP BY some_id;

Hint for future posting: give real DDL instead of a personal shorthand
that looks like C code. Do not tell people HOW you have decided to do
the problem; tell them WHAT you want.