SQL Subquery with OR condition-CodePudding

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

CodePudding user response：

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

CodePudding user response：

EDIT: If you want the names to only have Iphone or a combination of combination of Iphone and Samsung:

Try:

SELECT distinct name
FROM my_table
WHERE Brand IN (
               SELECT Brand
               FROM my_table
               WHERE brand='Iphone'
               GROUP BY Brand
               HAVING COUNT(*) = 1
                )
 OR   name in ( SELECT name
                FROM my_table
                WHERE brand in ('Iphone','Samsung')
                GROUP BY name
                 HAVING COUNT(brand)=2 
              ) 
;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/150

If you need names that at least have Iphone or a combination of Samsung and Iphone use:

 select distinct name
 from my_table
 where brand = 'Iphone' or name in ( SELECT name
                                    FROM my_table
                                    WHERE brand in ('Iphone','Samsung')
                                    GROUP BY name
                                    HAVING COUNT(brand)=2 ) ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/148

CodePudding user response：

Here's another solution:

SELECT name, GROUP_CONCAT(DISTINCT brand ORDER BY brand) AS brands
FROM mytable
GROUP BY name
HAVING brands IN ('Iphone', 'Iphone,Samsung');