CODEWITHSUNDEEP
sqloracle-databaseouter-join
xenres
xenres

Reputation: 59

Left Outer Join with IN operator

I am using Oracle SQL. Here are some example tables:

People
PersonID   Name
1          Elmo
2          Oscar
3          Chris

Attribute
PersonID   Attribute
1          Happy
1          Muppet
1          Popular
2          Grouchy
2          Muppet
2          Popular
3          Programmer

I want a list of people and I want to know whether we have knowledge of them being happy or grouchy. The following is the output I want:

Name       Mood
Elmo       Happy
Oscar      null (Happy)
Chris      null (Happy)

Elmo       null (Grouchy)
Oscar      Grouchy
Chris      null (Grouchy)

If I use this query, it will not return the additional null result rows that I want:

SELECT p.Name, a.Attribute
FROM People p
LEFT OUTER JOIN Attributes a
ON p.PersonID = a.PersonID AND a.Attribute IN ('Happy','Grouchy')

I know union would work. Is there any other way to do this ?

Pardon my laziness, I modified an already existing question.

Upvotes: 0

Views: 391

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You need to generate all the possible rows and then see which match. This uses a cross join and left join:

select p.name,
       (case when pm.mood is not null then pm.mood
             else 'NULL (' || m.mood || ')'
        end) as mood
from (select 'Happy' as mood from dual union all
      select 'Grouchy' from dual
     ) m cross join
     (select distinct p.name from people
     ) p left outer join
     People pm
     on pm.name = p.name and pm.mood = p.mood
order by m.mood, p.name;

Upvotes: 2

Related Questions