Solved

sql query based on results of another query

Posted on 2013-01-30
10
372 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now