SQL: Check if a row exists for each condition-CodePudding

I'm not able to find a correct SQL query for my needs. I have a table like this:

first_id | second_id | value
1          1           valueABC
1          2           valueLoL
2          1           valueBlaBla
2          2           valueLoL

Now I would like to select the first_ids where they have at least one row for EACH condition:

second_id = 1 and value = 'valueABC'
AND
second_id = 2 and value = 'valueLoL'

I already tried this, but the query is not correct:

select distinct first_id from myTable where (second_id = 1 and value = 'valueABC')
and (second_id = 2 and value = 'valueLoL') group by first_id having count(first_id) = 2

So in my example, first_id = 1 should be selected, as we have a row for each condition.

Can you help me please ? Thank you.

CodePudding user response：

I think you can do something like the following:

select first_id
from t
group by first_id
having 
    Max(case when second_id = 1 and value = 'valueABC' then 1 end) is not null and 
    Max(case when second_id = 2 and value = 'valueLoL' then 1 end) is not null;

CodePudding user response：

Try this:

select first_id from
(select first_id, 
sum(case when second_id = 1 and value = 'valueABC' then 1 else 0 end) as nbCond1,
sum(case when second_id = 2 and value = 'valueLoL' then 1 else 0 end) as nbCond2
from myTable
group by first_id) t
where nbCond1 > 0 and nbCond2 > 0;

Basically grouping by first_id and counting how many times the two conditions appear in a row for each first_id, then only selecting first_ids that have the number of occurences of both conditions > 0.

Fiddle