Selecting records that are value x or value x and y, but not value y only.

I have a tricky record selection question.  I'm trying to select from our database all records from the Installer table (which holds labor cost data) that reference Invoice Numbers that reference Product Code 84, Product Code 84 and 85, but not Product Code 85 only.  I don't believe my current SQL statement does this... I think it allows Invoice Number that reference exclusively Product Code 84, exclusively Product Code 85 or 84 and 85 together.  Any suggestions?
SELECT Installer.Inv_Num, Installer.Pr_Code, Installer.RegHours, Installer.OvrHours,
	Installer.DoubHours, Installer.MiscEarn, Installer.Rate, JobType.JobTypeFullName
FROM Installer, Header, JobType
WHERE Installer.Inv_Num = Header.Inv_Num AND Header.Job_Type = JobType.JobType AND
	NOT Installer.Inv_Num IN
	(
	SELECT Inv_Num
	FROM Installer
	WHERE NOT Pr_Code IN ('84', '85')
	)
AND LastName <> 'ASSESSMENT'
ORDER BY Installer.Inv_Num

Open in new window

gsszuberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:


SELECT i.Inv_Num, i.Pr_Code, i.RegHours, i.OvrHours,
      i.DoubHours, i.MiscEarn, i.Rate, jt.JobTypeFullName
FROM Installer i
JOIN Header h
  ON i.Inv_Num = h.Inv_Num 
JOIN JobType jt
  ON h.Job_Type = jt.JobType 
WHERE EXISTS( 
      SELECT null
      FROM Installer x
       WHERE x.Inv_Num = i.Inv_Num
        AND x.Pr_Code = '84'
      )
AND i.Pr_Code in ( 84', '85')
AND LastName <> 'ASSESSMENT'
ORDER BY i.Inv_Num

Open in new window

0
gsszuberAuthor Commented:
I can't say I totally understand what's going on here, but unforunately I don't think that worked, as it returrned more records than my initial query.  I've also gone back and checked a few of the invoices manually in the application and it I've found invoices that have Installer records that are not 84 or 85 (though they do still contain 84 and 85).  I'm not sure where to go from here other than to maybe use UNION ALL to join two queries -- one that selects Invoice numbers that reference 84 only and another that selects Invoice Numbers that reference 84 and 85 only.  Accomplishing the latter is where I come up short.  However, I would think you'd have to stay away from using IN, since it leaves the door open for Invoices containing 85 only.
0
eszaqCommented:
Try this:
SELECT Installer.Inv_Num, Installer.Pr_Code, Installer.RegHours, Installer.OvrHours,
      Installer.DoubHours, Installer.MiscEarn, Installer.Rate, JobType.JobTypeFullName
FROM Installer, Header, JobType
WHERE Installer.Inv_Num = Header.Inv_Num AND Header.Job_Type = JobType.JobType AND
      Installer.Inv_Num IN (84,85)
      AND EXISTS
      (
      SELECT Inv_Num
      FROM Installer
      WHERE Pr_Code =84
      )
AND LastName <> 'ASSESSMENT'
ORDER BY Installer.Inv_Num
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>and it I've found invoices that have Installer records that are not 84 or 85
(though they do still contain 84 and 85).

can you clarify that? as for me that is a contradiction...
0
eszaqCommented:
Possible correction to my snippet. I don't know much about your data, but you probably need to use DISTINCT inside EXISTS (to limit total number of records returned, just in case if there might be multiple rows with the same Inv_Num)
0
gsszuberAuthor Commented:
angelIII, I meant in my earlier comment that there were invoices with a product code other than 84 or 85 associated with them, such as 82 or 89.

eszaq, your query didn't seem to return any records.  You are correct that multiple records with the same invoice number, but I do want to keep them all.  I'm going to be summarizing them later when I feel confident I'm selecting the correct records.  Also, Prod_Code is actually a varchar(3) so you need single quotes around 84 and 85.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the query I posted should only give you the 84 and 85 Pr_Codes, due to this part in the query:
AND i.Pr_Code in ( '84', '85')

so, i don't see how other values can be returned...
0
gsszuberAuthor Commented:
You are correct, I don't believe any rows that contain Pr_Code values other than 84 or 85 are returned by the query.  However, of the invoice numbers selected, some of them also have rows that, while they were not selected by your query, have Pr_Code values other than 84 or 85.  It's confusing, I know.

Maybe I can reword what I need.  We need all invoices that are exclusively 84 and 85.  We can allow invoices that are exclusively 84, but not those that are exclusively 85.  I believe my query does all but the latter.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the word "exclusively" is the key to the syntax you need:
SELECT i.Inv_Num, i.Pr_Code, i.RegHours, i.OvrHours,
      i.DoubHours, i.MiscEarn, i.Rate, jt.JobTypeFullName
FROM Installer i
JOIN Header h
  ON i.Inv_Num = h.Inv_Num 
JOIN JobType jt
  ON h.Job_Type = jt.JobType 
WHERE EXISTS( 
      SELECT null
      FROM Installer x
       WHERE x.Inv_Num = i.Inv_Num
        AND x.Pr_Code = '84'
      )
AND NOT  EXISTS( 
      SELECT null
      FROM Installer x
       WHERE x.Inv_Num = i.Inv_Num
        AND x.Pr_Code NOT IN ('84', '85') 
      )
AND i.Pr_Code in ( 84', '85')
AND LastName <> 'ASSESSMENT'
ORDER BY i.Inv_Num

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gsszuberAuthor Commented:
You're the man!  So far everything is checking out great.  Thanks for your help on this and sorry for the confusion... apparently I need to familiarize myself with WHERE EXISTS.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.