Help with select - 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

Help with select

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2009-12-15 : 09:50:29

I have three tables that I want to use to insert data into a new table (newTable).
table1 contains all data.
table2 and table3 contains data that shall be removed from insert.

table1
fnr (unique)
id1 (unique together with id2)
id2 (unique together with id1)
col1
col2

table2 contains data that shall be removed from insert
fnr (pk)
col1
col2

table3 contains data that shall be removed from insert
id1 (unique together with id2)
id2 (unique together with id1)
col1

INSERT INTO newTable(id1,id2,col1,col2)
(select
id1
,id2
,col1
,col2
from table1
where fnr not in (select fnr from table2)
and
--Dont know how to write the following!
table1.id1, table1.id2 not in (select table3.id1, table3.id2 from table3)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 10:02:47

[code]select
id1
,id2
,col1
,col2
from table1 a
left join table2 b on a.fnr = b.fnr
left join table3 c on a.id1 = c.id1 and a.id2 = c.id2
where b.fnr is null and c.id1 is null and c.id2 is null[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources