Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql query based on results of another query

Posted on 2013-01-30
10
Medium Priority
?
422 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 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 800 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 800 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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