Join of 3 tables

[lyric0n]

Hello,
I currently have three tables as described below and I need to get the following results when I run a query on them but can't figure out how the query should look. Basically, it will be a join on table A and B, but finding rows in C which are not found in that particular join.

Table A
ID
1
2
3
6

Table B
1
2
3
4

Table C
1
2
3
4
5
6

In this case, the desired out put would only be the record 5.

Thanks for the help,
Chris

 

[gmmastros]

I suggest you union table a bn b together, then do a loeft join with table c to get the id's that are missing. Like this...

[COLOR=blue]Declare[/color] @TableA [COLOR=blue]Table[/color] (id [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](6)

[COLOR=blue]Declare[/color] @TableB [COLOR=blue]Table[/color](id [COLOR=blue]int[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableB [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableB [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableB [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableB [COLOR=blue]Values[/color](4)

[COLOR=blue]Declare[/color] @TableC [COLOR=blue]Table[/color](id [COLOR=blue]int[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableC [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableC [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableC [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableC [COLOR=blue]Values[/color](4)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableC [COLOR=blue]Values[/color](5)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableC [COLOR=blue]Values[/color](6)

[COLOR=blue]Select[/color] C.id
[COLOR=blue]From[/color]   @TableC [COLOR=blue]As[/color] C
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] Id [COLOR=blue]From[/color] @TableA
         Union
         [COLOR=blue]Select[/color] Id [COLOR=blue]From[/color] @TableB
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] C.Id = A.Id
[COLOR=blue]Where[/color]  A.Id [COLOR=blue]Is[/color] NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nomadicalloy]

good answer.

This is Signature

http://www.xpherion.com/

 

[SQLDenis]

Mmmm

it will be a join on table A and B, but finding rows in C which are not found in that particular join.

if you join a and b only rows 1,2 and 3 are returned if you join c on that then 4,5 and 6 should be the correct answer

Insert Into @TableB Values(2)
Insert Into @TableB Values(3)
Insert Into @TableB Values(4)

Declare @TableC Table(id int)
Insert Into @TableC Values(1)
Insert Into @TableC Values(2)
Insert Into @TableC Values(3)
Insert Into @TableC Values(4)
Insert Into @TableC Values(5)
Insert Into @TableC Values(6)


--5 only
Select C.id
From   @TableC As C
where not exists(select * from @TableA a where a.id = c.id)
AND not exists(select * from @TableB b where b.id = c.id)

-- 4,5 and 6
Select C.id
From   @TableC As C
where not exists(select * from @TableA a where a.id = c.id)
or not exists(select * from @TableB b where b.id = c.id)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[clanguage1]

Try this
select id from c
where id not in (select id from a)
and id not in ( select id from b)
That should get you what you are looking for .
Good luck


clanguage

I count my blessings, daily. CJM.