Need to filter out conditional values from MySql table

user_9556314 0
Viewed 47

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.

having group-by sum mysql sql
2 Answers

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.

user_104988280

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')
                )
user_71370900
Related