Filter based on value for each value in second column-CodePudding

This is my input table which has distinct events for each site.

Event	Site
R1	Site A
R2	Site A
R3	Site A
R4	Site A
R1	Site B
R2	Site B
R3	Site B
R1	Site C
R2	Site C
R3	Site C
R4	Site C
R1	Site D
R2	Site D

I am trying to classify sites based on the event occurrence. From the above input table I want to get answers like

Sites having R4 events --> Site A and Site C

R4 occurrence sites
Site A
Site C

Sites not having R4 events --> Site B and Site C

R3 and below occurrence sites
Site B
Site C

Sites having only R2 or R1 events --> Site D

R1 and R2 only occurrence sites
Site D

I have tried using

To get Sites having R4 events

select site where event = 'R4'

To get Sites not having R4 events

select site where event not in ('R4')

To get Sites having only R2 or R1 events

select site where event not in ('R4','R3')

I also tried using count approach and it gives me results but I cannot use them as some times all events occur for a site except the first one, so it will not be a correct result.

CodePudding user response：

A general approach for finding sites matching a set of events, e.g. for events R2, R3, and R4:

SELECT Site
FROM yourTable
WHERE Event IN ('R2', 'R3', 'R4')
GROUP BY Site
HAVING COUNT(DISTINCT Event) = 3;

CodePudding user response：

To get Sites having R4 events (may also have other/non-R4 events):

select distinct site from yourtable where event = 'R4'

To get Sites not having R4 events (but they must have other events)

select distinct site from yourtable where event not in ('R4')

To get Sites having only R2 or R1 events (they should not have other events)

select distinct site 
from yourtable yta 
where event in ('R2','R1')
and not exists (select * 
                from yourtable ytb
                where yta.site=ytb.site 
                and ytb.event not in ('R2','R1')
               )