subtracting the results - 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

subtracting the results

Author

Topic

jim_cool
Starting Member

43 Posts

Posted - 2006-07-21 : 05:39:52

hi all
I want to find the set of rows that are present in result returned by first select statement & that are not there in the result of second select statement.
Both select statement operate on tables having similar columns.
Is there any way by which i can find such difference in results?
Is this possible using EXCEPT function?
plz help me.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-21 : 05:42:36

(select .......) t1
left join
(select .......) t2
on t1.col1 = t2.col1
and t1.col2 = t2.col2
and .....
where t2.col1 is null

you might also have to check for nulls.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 05:42:57

NOT EXISTS is what you need.

From BOL...

quote:
E. Use NOT EXISTS
NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery. This example finds the names of publishers who do not publish business books.

USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
ORDER BY pub_name
GO

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 05:42:59

you can use NOT EXISTS or NOT IN depending on your situation
EDIT :

jim_cool
Starting Member

43 Posts

Posted - 2006-07-21 : 05:54:05

hi
First of all , i will like to thank u all for such a immediate response & providing me the solution.
Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-21 : 06:01:18

Is that a record?
3 reponses within 4 mins and all within 23 secs of each other.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 06:10:24

quote:
Originally posted by nr

Is that a record?
3 reponses within 4 mins and all within 23 secs of each other.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I doubt it!

I think we're all a bit too keen...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources