Solved

sql query based on results of another query

Posted on 2013-01-30
10
420 Views
Last Modified: 2013-01-31
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
0
Comment
Question by:cycledude
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38835842
SELECT People.*
FROM People
LEFT JOIN Documents
ON People.PeopleID = Documents.PeopleID
WHERE Documents.PeopleID IS NULL
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38835849
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38835860
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

0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 38835868
The above experts are correct.

This might help get your brain wrapped around these types of issues...

SQL Joins
0
 

Author Comment

by:cycledude
ID: 38835892
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38835946
Try:

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

Assisted Solution

by:Dale Fye
Dale Fye earned 200 total points
ID: 38835953
But if your goal is to identify those employees who do not specifically have a document of a specific DocTypeID then you will need:

select * from employees
LEFT JOIN (SELECT EmployeeID, DocTypeID FROM documents WHERE DocTypeID = 12) as D
ON employees.id = D.employeeid
WHERE D.doctypid IS NULL
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 38836123
same idea with my suggested syntax:
SELECT p.*
FROM People p
WHERE NOT EXISTS( SELECT NULL FROM Documents d
WHERE p.PeopleID = d.PeopleID
  AND d.DocTypeID = 12
)

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38836184
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.
0
 

Author Closing Comment

by:cycledude
ID: 38838793
Thanks for the help ;o)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question