Search code examples
mysqlsqlsubquery

SQL Subquery with OR condition

I have the following schema:

+--------+---------+
|Name    |    Brand|
+--------+---------|
|John    |   Iphone|
|John    |  Samsung|
|Carl    |   Xiaomi|
|Natan   |   Iphone|
|Julie   |  Samsung|
+--------+---------+

In my result I need to return the names which frame in these 2 scenarios:

1- Have only Iphone
OR
2 - Have a exclusive combination of Iphone and Samsung; This way, I need this output:

+--------+
|Name    |
+--------+
|John    |
|Natan   |
+--------+

This is what I tried, but with no success:

select name
from schema
where brand = 'Iphone' or
brand in 
    (select brand
    from schema
    where brand = 'Iphone' and brand = 'Samsung')
Solution

  • You could try using union between the iphone olny and the pair iphone-samsung

        select name
    from schema
    where brand = 'Iphone' 
    union 
    select name 
    from schema
    where brand in  ('Iphone' , 'Samsung') 
    group by name
    having count(distinct brand) = 2