Search code examples
mysqlsqlgroup-bysumhaving

Need to filter out conditional values from MySql table

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:
  1. source has one or more combinations of - Raja, Ravi or Sam

And

  • Exclude the ID if:
  1. 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.

Solution

  • 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.