Filtering by two columns in subquery

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

SQL Server 2005 Forums

Transact-SQL (2005)

Filtering by two columns in subquery

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-09-04 : 01:19:40

Here is an example!

select * from table
where table.column1 and table.column2
not in (select column1 and column2 from table2)

i wonder how could i do that? anyone?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 01:22:27

select t.* from table t
left join table2 t2 on t.column1 = t2.column1 and t.column2 = t2.column2
where t2.column1 is null

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-09-04 : 07:39:07

hello,

left join returns all rows from the table1. right? what i want to do is to eliminate all the rows from the table1 where the column1 and column exists in the sub query.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 07:42:32

did u see the my query by running it.
left join will return all the rowws from table1 and where t2.column1 is null
condition will return the which rows are not in the table2 that rows will be displayed in the result check it once.

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-09-04 : 11:24:22

i did it but the records i didn't want to show on table1 still shows.
here is an example data
--TABLE1--
column1 column2
data1 data2
data3 data4

--TABLE2--
column1 column2
data3 data4

--expected output--
column1 column2
data1 data2

i am still working here to achieve this output your help is very much appreciated..

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-04 : 11:31:46

did you actually run the query bklr gave?

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-09-04 : 11:44:25

hello,

sorry if i confused you. bklr scripts work. i didn't know that "where table.column is null" was different from "and table.column is null"

bklr thanks. :D

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 22:55:19

quote:
Originally posted by k_cire0426

hello,

sorry if i confused you. bklr scripts work. i didn't know that "where table.column is null" was different from "and table.column is null"

bklr thanks. :D

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources