CODEWITHSUNDEEP
sqldatabaseselectleft-join
SpeakHearSeeNoEvil
SpeakHearSeeNoEvil

Reputation: 3

Select unique data from one table

I have a diners table:

NAME     |   FOOD
-----------------
matthew  |   rice
matthew  |   beans
mark     |   rice
mark     |   beans
Luke     |   rice
john     |   beans

I need to extract names that have had only rice, names that have had only beans and both. A LEFT JOIN would work if they were in separate tables. But I'm having difficulty because they're in one table.

I've tried variants of these 2 statements with no luck:

SELECT name 
FROM diners 
WHERE NOT EXISTS 
    (SELECT name 
     FROM diners
     WHERE food = 'beans')

SELECT t1.name FROM diners AS t1
LEFT JOIN diners AS t2 ON t1.name = t2.name
WHERE t2.food = 'rice'
  AND t2.name IS NULL

Upvotes: 0

Views: 120

Answers (5)

manurajhada
manurajhada

Reputation: 5380

Had Rice only --

    select name from dinner where food='rice' and name in 
    (select name from diners group by name having count(name)=1);

Had Beans only --

   select name from dinner where food='beans' and name in 
   (select name from diners group by name having count(name)=1);

Had Both --

   select name from diners where food in ('rice','beans') group by name 
   having count(name)>1;

Upvotes: 1

PaddyC
PaddyC

Reputation: 576

To get the diners that have only had 'beans':

SELECT  distinct name 
FROM    DINERS D1
WHERE NOT EXISTS
(SELECT NULL FROM DINERS D2 WHERE FOOD = 'rice' AND D1.NAME = D2.NAME)
 AND EXISTS 
 (SELECT * FROM DINERS D2 WHERE FOOD = 'beans' AND D1.NAME = D2.NAME);

To get the diners that have only had 'rice':

SELECT  distinct name 
FROM    DINERS D1
WHERE NOT EXISTS
(SELECT NULL FROM DINERS D2 WHERE FOOD = 'beans' AND D1.NAME = D2.NAME)
 AND EXISTS 
 (SELECT * FROM DINERS D2 WHERE FOOD = 'rice' AND D1.NAME = D2.NAME);

To get only the diners that have had both:

SELECT  distinct name 
FROM    DINERS D1
WHERE EXISTS
(SELECT * FROM DINERS D2 WHERE FOOD = 'rice' AND D1.NAME = D2.NAME)
AND EXISTS 
(SELECT * FROM DINERS D2 WHERE FOOD = 'beans' AND D1.NAME = D2.NAME);

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24086

-- names that have had BOTH

SELECT NAME FROM diners
WHERE FOOD IN ('RICE','BEANS')
GROUP BY NAME
HAVING COUNT(*)>1

-- names that have had only rice

SELECT NAME FROM diners
WHERE NAME NOT IN (
SELECT NAME FROM diners
WHERE FOOD NOT IN ('RICE'))

--names that have had only beans

SELECT NAME FROM diners
WHERE NAME NOT IN (
SELECT NAME FROM diners
WHERE FOOD NOT IN ('BEANS'))

Upvotes: 1

janb
janb

Reputation: 370

How about using GROUP_CONCAT?

SELECT name GROUP_CONCAT(food) AS foodchain FROM diners GROUP BY name

You can then check if foodchain equals rice or beans or whatever in your application. There's likely an easy solution within SQL but this should work too.

Upvotes: 0

paul
paul

Reputation: 22011

    create table #diners (name nvarchar(20), food nvarchar(20))
    insert into #diners values ('matthew','rice')
    insert into #diners values ('matthew','beans')
    insert into #diners values ('mark','rice')
    insert into #diners values ('mark','beans')
    insert into #diners values ('luke','rice')
    insert into #diners values ('john','beans')


    --  only rice
    select  d1.name
    from        #diners d1 
    where       d1.food = 'rice' and
            d1.name not in (select d2.name from #diners d2 where d2.food != 'rice')

    --  only beans
    select  d1.name
    from        #diners d1 
    where       d1.food = 'beans' and
            d1.name not in (select d2.name from #diners d2 where d2.food != 'beans')

    --  both
    select  d1.name
    from        #diners d1 
    where       d1.food = 'rice' and
            d1.name in (select d2.name from #diners d2 where d2.food = 'beans')

Upvotes: 0

Related Questions