You are here:

MS SQL/Complex SQL Query

Advertisement


Question
Hello!  I am having a hard time with a query to get the results I am looking for.  I have a table that has multiple records like below.  I am wanting a query that will show me all people that do NOT have an I-9 form.  I can easily create a query to show me records with no I-9 form...however, the results show all the other forms so the incorrect people are on it since the people in the results have other forms in their name.  I only want it to show people who do not have an I-9 form at all (and no other people).  If that person HAS an I-9 form and still has other forms, I do not want that person to show at all.  (in my example only John Smith should show up - Amy Rogers should not show up because she has an I-9 form). Hope that makes sense, I am racking my poor brain for hours on this one!

FirstName  LastName  FormType
Amy        Rogers    W-2
Amy        Rogers    Drivers License
Amy        Rogers    I-9
Amy        Rogers    Drug Form
John       Smith     Drivers License
John       Smith     W-2

Answer
Dear Kristin,

This is an example of a need where we want to use SELF JOIN.

First find all first and last name which have I9 form and get all except those guys.

This might work for you:

SELECT a.FirstName, b.LastName
FROM TABLENAME A
LEFT JOIN (SELSCT FIRSTNAME, LASTNAME FROM TABLENAME WHERE FORMTYPE = 'I-9') B
ON A.FIRSTNAME =B.FIRSTNAME
AND A.LASTNAME = B.LASTNAME
WHERE B.FISRTNAME IS NULL
AND B.LASTNAME IS NULL

please feel free to followup  

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jwalant Natvarlal Soneji

Expertise

Questions related to query optimization, finding complex results.

Experience

Experience in the area: I have been working in the field since 2005 year. Education/Credentials: BE IT, First Class, 2005 Batch.

Publications
http://jwalantsoneji.com

Education/Credentials
BE IT, India

Awards and Honors
MCP

©2016 About.com. All rights reserved.