query to exclude a table - 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

query to exclude a table

Author

Topic

ConnieXujuan
Starting Member

48 Posts

Posted - 2011-12-28 : 23:37:47

Hi -,

I created two tem tables #t1, #t2 and populate both tables from table [TableA]. #t1 contains all the data from [tableA] and #t2 only contains part of the data from [TableA].

queryies are as below:

select * into #t1 from tableA

select * into #t2 from TableA where COLUMN1<100

I want to get the part of data that is in #t1 but not in #t2 so I came up with the query below:

select * from #t1 where not exists (select * from #t2)

however, the query did gave me nothing as running results. I was just wondering what was the reason for it?

and if I want to get the part of data that is in #t1 but not in #t2, how should my query be like?

Thanks a lot!

Connie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-28 : 23:52:22

[code]
select * from #t1 t1 where not exists (select * from #t2 t2 where t2.somecol = t1.somecol)
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 23:53:37

it should be


select * from #t1 t where not exists (select * from #t2 where pk=t.pk)

where pk is primary key

other way is

select * from #t1
EXCEPT
select * from #t2

i hope this was just for learning it otherwise for scenario above you dont even require temporary table
you just need this

select *  from TableA where COLUMN1>=100

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConnieXujuan
Starting Member

48 Posts

Posted - 2011-12-29 : 06:15:19

Thanks guys!!

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 09:16:04

quote:
Originally posted by visakh16
select * from TableA where COLUMN1>=100
[/code]

SQL Server MVP
http://visakhm.blogspot.com/

well...duh..why do people make things more complicated thann they need to be?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-01 : 02:20:42

I hope as i suggested it was for learning exercise

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources