CODEWITHSUNDEEP
sqloracle-database
user4586533
user4586533

Reputation: 33

SQL - select group of records that only meet certain criteria

I'm trying to figure out how to write a query to select a group of records that only have a certain condition that is true. Here is an example. I have a table that has a list of boats and the owners. When a boat gets sold to a new owner, a new record is inserted and the CURRENTOWNER flag changes to Y:

OWNERID   BOATID    CURRENTOWNER
------   --------   ------------
OWN1       NUM1          Y
OWN2       NUM1          N
OWN3       NUM1          N
OWN4       NUM2          N
OWN5       NUM2          N
OWN6       NUM2          N
OWN7       NUM3          Y

I'm trying to write query that will show me only the BOATID's that have multiple owners (e.g. NUM1 or NUM2) and also do not have a current owner, meaning for that particular BOATID, there are no records where CURRENTOWNER = 'Y'. From the data above, the query should return the NUM2 since it does not have a current owner.

I know I can get the list of boats that have had multiple owners using:

select boatid from boattable group by boatid having count(*) >1

After that I'm unsure how to only get the records which have no current owner.

Upvotes: 2

Views: 6574

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can do this with conditional aggregation and a having clause:

select boatid
from boattable
group by boatid
having count(*) > 1 and
       sum(case when current_owner = 'Y' then 1 else 0 end) = 0;

This checks that there is more than one owner and non of them are current.

Note: Given the structure of your data, you could write:

having count(*) > 1 and max(current_owner) = 'N'

However, I think the first version is clearer on the intent and less susceptible to errors if new values appear in the current_owner column.

Upvotes: 5

James Z
James Z

Reputation: 12317

This should do it:

select 
  BOATID
from 
  boattable b
where 
  not exists (
    select 1 from boattable b2
    where b2.BOATID = b.BOATID and b2.CURRENTOWNER = 'Y')
group by
  BOATID
having
  count(*) > 1

Upvotes: 3

Related Questions