CODEWITHSUNDEEP
sqlsql-server
sodhosdh
sodhosdh

Reputation: 25

Return Values from one table that have no entries in another?

In this example, I'm trying to return a list of values (in this case, company names) that have no entries in another table (entries in this case meaning invoices). In other words, I'm trying to return a list of companies that have no invoices. Here is my code:

Select CompanyName From tblCompany join tblInvoice ON tblCompany.CompanyID = tblInvoice.CompanyID Where tblCompany.CompanyID NOT IN (Select CompanyID From tblInvoice)

What I'm trying to get is this:

Desired Results

However, when I run the code, no values show up. Can anyone tell me why?

Upvotes: 1

Views: 46

Answers (3)

Mayur Sawant
Mayur Sawant

Reputation: 11

Use the below query for desired results.

Select CompanyName
From tblCompany 
left join tblInvoice
ON tblCompany.CompanyID = tblInvoice.CompanyID 
Where tblInvoice.CompanyID IS NULL

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Try this

Select  CompanyName
From    tblCompany 
Where   tblCompany.CompanyID NOT IN    
    (   Select  CompanyID
        From    tblInvoice)

That is get all the CompanyName from tblCompany where the CompanyID not exists in the tblInvoice.

Or you can try the below one,

select CompanyName
from   tblCompany
left join tblInvoice on tblCompany.CompanyID = tblInvoice.CompanyID 
where  tblInvoice.CompanyID is null

Upvotes: 1

Bohemian
Bohemian

Reputation: 425003

Use left join and filter on nulls:

select CompanyName
from tblCompany
left join tblInvoice on tblCompany.CompanyID = tblInvoice.CompanyID 
where tblInvoice.CompanyID is null

This works because missed joins return nulls in the joined table's values.

Upvotes: 1

Related Questions