Exclude records - 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

Exclude records

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2013-09-19 : 14:28:37

I am trying to get this query to work, but if keeps running and nothing returns, however runnign both separate work correctly.

select code, AMOUNT
from vouchers v
where TYPE= 'DEBIT'
and CODE = '1234'
and EXPIRATION > '06/30/2013'
and code not in
(select code from vouchers
inner join purchases
on CODE = CODE
)

Can someone help?

Thanks
D

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 14:35:39

The "CODE = CODE" is the problem. That will always evaluate to true. Is there a column CODE in purchases table as well? It doesn't seem like it, because it did, then the query would not have compiled. What is/are the column(s) that is/are common to vouchers and purchases that you are trying to join on?

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 14:37:15

select code, AMOUNT
from vouchers v
where TYPE= 'DEBIT'
and CODE = '1234'
and EXPIRATION > '06/30/2013'
and code not in
(select code from purchases)

dzabor
Posting Yak Master

138 Posts

Posted - 2013-09-19 : 14:47:54

Thanks - I was able to get the data using a left join with nulls as well.

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 15:08:37

So your query must be like this?

select code, AMOUNT
from vouchers v
left join purchases p
on c.code = p.code
where TYPE= 'DEBIT'
and CODE = '1234'
and EXPIRATION > '06/30/2013'
and p.code is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources