IN Operator - 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

SQL Server 2005 Forums

Transact-SQL (2005)

IN Operator

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2010-03-04 : 13:14:05

Good People of ST

I am somehow stuck with this conversion. I have a PL-SQL script to convert to TSQL ..

Kindly examine this snippet


select *
from
myTable a
where
((a.date , a.id) not in (select date,id from Mytable2 b)

1. In TSQL, the IN operator only takes on field.

2. Please how can I convert the above code to TSQL or

3. How can I use more than one fields in IN operator.

4. EXCEPT command will not work because am taking more fields from MyTable.

Many Thanks

I sign for fame not for shame but all the same, I sign my name.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 13:23:21

[code]select *
from myTable a
where not exists (select * from Mytable2 b where a.date = b.date and a.id = b.id)[/code]
EDIT : or
[code]select a.*
from myTable a
left join Mytable2 b on a.date = b.date and a.id = b.id
where b.id is null[/code]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-04 : 13:34:43

quote:
Originally posted by abacusdotcom

Good People of ST

4. EXCEPT command will not work because am taking more fields from MyTable.

SELECT a.*
FROM
myTable a
JOIN (SELECT date, id FROM myTable
      EXCEPT
      SELECT date, id FROM mytable2) D(date, id) ON a.date = d.date AND a.id = d.id

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2010-03-05 : 04:50:17

Many Thanks, Boss!!!!!!!!!!
I appreciate..

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources