[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

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

0
gsszuber
Asked:
gsszuber
  • 4
  • 4
  • 2
1 Solution
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now