sodhosdh sodhosdh - 2 years ago 74
SQL Question

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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download