CODEWITHSUNDEEP
sql-serversql-server-2008
Rasanja Dampriya
Rasanja Dampriya

Reputation: 55

how to find 0 value records by comparing two tables

The question is "find project managers those who don't manage any projects , list name and id"

Project manager table and Projects table

first table is "PM" and second table is "Projects" How to write this query ? I tried the following query but it didnt work.

select a.ID,a.name 
from PM a,Projects b
where a.ID=b.ID
group by a.name
having COUNT(b.ID)=0;

Upvotes: 0

Views: 49

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

Can't you just do a left join here:

SELECT
    a.ID, a.name 
FROM PM a
LEFT JOIN Projects b
    ON a.ID = b.ID
WHERE b.ID IS NULL

But your table structure is not at all clear to me. It is confusing to have the manager ID column have the same name in both tables.

Also, you were doing an implicit inner join in your current query. Not evil, but it certainly makes it harder to see how you are relating the two tables. With an explicit left join hopefully it is clear what is going on here. Those manager records in PM which have no projects would therefore match to no project records. The marker for this would be a (single) manager record all of whose project columns are null.

Upvotes: 3

Related Questions