Solved

sql query based on results of another query

Posted on 2013-01-30
10
407 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38835842
SELECT People.*
FROM People
LEFT JOIN Documents
ON People.PeopleID = Documents.PeopleID
WHERE Documents.PeopleID IS NULL
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 65

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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38835946
Try:

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

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

685 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