Sql to select records that are not include on a specific table. 2

[jonasggg]

Hello.

I would like to select records that are not include on a table. For example, a Client ID that is not included on an invoice table, etc. Can I use Count function?

Thanks in advace.

 

[NoCoolHandle]

Select * from clients where clientid not in (select clientid from invoice)

 

[jbenson001]

Select ClientID
From table1
Where NOT EXISTS( Select * From Invoice )

 

[NoCoolHandle]

jbenson, how can the "exists" work without the inner query being corealated?

Dont you mean


Select ClientID
From table1
Where NOT EXISTS( Select * From Invoice [red]where invoice.invoiceid <> table1.invoiceid[/red] )

 

[NoCoolHandle]

cRAP@! I mean

Select ClientID
From table1
Where NOT EXISTS( Select * From Invoice where invoice.invoiceid [red]=[/red] table1.invoiceid )

 

[NoCoolHandle]

For instance

use pubs
go
select * from titles t where not exists 
(select * from sales where t.title_id = sales.title_id)

returns the two rows that reflect books that havn't sold

but

use pubs
go
select * from titles t where not exists 
(select * from sales)

Returns no rows..

 

[jbenson001]

Actually I made a mistake. Typed too quickly, I was just about to post the same answer you had using the IN clause. However your second piece of code does work as well.

Jim