CODEWITHSUNDEEP
sqloracle-database
jim collins
jim collins

Reputation: 417

Oracle specific selection

I have a table as follows

OWNERID 
PET_QUANTITY 
PET_TYPE

PET_TYPE is either DOG, CAT, or BIRD

There is one record per owner/pet so if have a dog and bird there would be two records and pet_quantity would be 2 in both records.

I want all the owner_IDs where the person has two pets and the pets are a dog and a cat.

Upvotes: 0

Views: 62

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

First off, the data model is rather unfortunate since the value for PET_QUANTITY for a particular row depends on data in other rows. That violates basic normalization rules. And that, in turn, creates a situation where you will inevitably get out of sync and so you have to determine whether the PET_QUANTITY rules or whether the number of rows in the table rules. You'd be much better served by a properly normalized data model.

Assuming that PET_QUANTITY rules when there is a discrepancy, one option would be to use an INTERSECT

SELECT owner_id
  FROM your_table
 WHERE pet_quantity = 2
   AND pet_type = 'CAT'
INTERSECT
SELECT owner_id
  FROM your_table
 WHERE pet_quantity = 2
   AND pet_type = 'DOG'

Another would be to do something like

SELECT owner_id, COUNT(DISTINCT pet_type)
  FROM your_table
 WHERE pet_quantity = 2
   AND pet_type IN ('DOG', 'CAT')
 GROUP BY owner_id
HAVING COUNT(DISTINCT pet_type) = 2

Upvotes: 5

Related Questions