using join instead of subselect

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 2000 Forums

Transact-SQL (2000)

using join instead of subselect

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-05 : 10:47:54

Ruel writes "Hi,

I'm trying to find out if there is a way for me to revised this query.

Select *
from table1
where productID not in (
select productID
from table1
where eventID <> 67)
and eventid is null

where the possible values of eventid are:
NULL,35,67,50

table1 could have several instances of productID with several instances of eventid

The query basically returns all productid in table that have eventid equal to null but should not have any instances where it had 67 as its eventid.

I had read something from outer rdbms product about exception join where it returns all records in the left table that does not appear on the right table. Seems that this is what I am looking for, but could not implement it in SQL server.

Hope you could help me with this.

Thanks in advance.

-Ruel :)"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-05 : 13:46:52

This should do it

SELECT t1.* 
FROM table1 t1
LEFT OUTER JOIN table1 t2 ON t2.productid = t1.productid AND t2.eventid = 67
WHERE t1.eventid IS NULL AND t2.eventid IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources