Solved

sql query based on results of another query

Posted on 2013-01-30
10
393 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 142

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 142

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

803 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