Subquery help - 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

Subquery help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-31 : 09:59:55

I want to know all records that are present in one table that do not exist in another table.I tried to use subquery but dont have the expertise to finish the query to get what Iam looking for

ReqNo is the common column in TableA and TableB

Need help

Below is the query where Iam stuck

Select * from Table A where reqno in (Select distinct reqno from TableB where date > '1/1/2011')
and reqno not in Table A

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-31 : 10:04:00

To get the reqno in tableA that are not in TableB

SELECT <columns from table>
FROM TableA a
WHERE NOT EXISTS(select * from TableB b where a.reqno = b.reqno)

Jim

Everyday I learn something that somebody else already knew

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-31 : 10:37:13

Works for me.Thanks Again

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-31 : 10:49:23

SELECT req_nbr FROM A
EXCEPT
SELECT req_nbr FROM B

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources