asked on

sql query based on results of another query

Hi

I have a query which is identifying a match between 2 tables.

lets say its employees and documents. The scenario is, every employee CAN have a document but it is not neccessary for them to have one.

I have a query which picks out all of the employees who have received a document, this was easy... but we need to know the opposite... ie which employees have not received the document..

how do I structure my second query (assuming I need one) so that it says

show me all employees not in the results of the other query?

Cheers

CD

Dale Fye

SELECT People.*
FROM People
LEFT JOIN Documents
ON People.PeopleID = Documents.PeopleID
WHERE Documents.PeopleID IS NULL

Dale Fye

The use of the LEFT JOIN says to include all the records from the People table, regardless of whether there is a matching record in Documents or not. Then, the WHERE clause specifically says to include only those without a match in the Documents table.

Guy Hengel [angelIII / a3]

another syntax would be this (with exactly the same results, and normally the same performance:

SELECT p.*
FROM People p
WHERE NOT EXISTS( SELECT NULL FROM Documents d
WHERE p.PeopleID = d.PeopleID
)

Open in new window

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cycledude

ASKER

Hi fyed

Thanks for the input, but it didn't work.. maybe I should elaborate more

employees

id
fname
sname

documents

id
employeeid
doc_name
doctypeid

relationship is one-to-many on the documents table, each employee can receive 1 or more of many document types.

so my simple query would be

select * from employees
INNER JOIN documents ON employees.id = documents.employeeid
WHERE doctypid = 12

Dale Fye

Try:

select * from employees
LEFT JOIN documents
ON employees.id = documents.employeeid
WHERE doctypid IS NULL

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Angel,

What is the advantage of using Exists or Not Exists over using the Inner/Left Join? Do the query plans differ, or is it more of a personal preference thing?

I went years writing SQL for Access before I even encountered the EXISTS clause, and by then, I had pretty much settled on a technique. I must admit that I really need to expand my knowledge of SQL Server syntax as it has been about 4 or 5 years since I have done much of anything in SQL Server.

cycledude

ASKER

Thanks for the help ;o)