Need to filter out conditional values from MySql table-CodePudding

I stuck with this kind of scenario wherein I need to extract the IDs based on this logic.

In this example, I want to extract the following combination in the output:

INCLUDE the result if:

source has one or more combinations of - Raja, Ravi or Sam

And

Exclude the ID if:

Source has one or more combinations of - Jane, Jake, or Jude.

ID	Source
1	Raja
1	Ravi
2	Sam
2	Raja
3	Jake
3	Raja
3	Sam
3	Jane
4	Sam
4	Jake
4	Jude

Output, I'm expecting as:

ID
1
2

This source table will always have more than 1 source value for each id.

Thanks in advance.

CodePudding user response：

Use aggregation and set the conditions in the HAVING clause:

SELECT ID
FROM tablename
GROUP BY ID
HAVING SUM(Source IN ('Raja', 'Ravi', 'Sam')) > 0
   AND SUM(Source IN ('Jane', 'Jake', 'Jude')) = 0;

See the demo.

CodePudding user response：

a good example to use EXISTS and NOT EXISTS.

Here is a way to do this

select distinct t.id
  from table t
where exists (select 1
                from table t2
               where t2.id=t.id
                 and t2.name in ('Raja','Ravi','Sam')
 and not exists(select 1
                  from table t3
                 where t3.id=t.id
                   and t3.name in ('Jane','Jake','Jude')
                )