I am using Oracle SQL, so outer joins have the nice (+) syntax. I should warn you that I am not allowed to redesign the database; I work for a large organization.
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 Grouchy
Chris
So here is the query I thought I would use:
SELECT p.Name, a.Attribute
FROM People p, Attributes a
WHERE p.PersonID = a.PersonID (+)
AND ( a.Attribute (+) = 'Happy'
OR a.Attribute (+) = 'Grouchy' )
(Perhaps I would have to put "OR a.Attribute IS NULL" or something.)
But in fact I'm not allowed to use OR inside an outer join at all! What should I actually do instead?
First of all, why can't you use proper OUTER JOIN
s?, you can use them in Oracle without having to do the implicit joins with the (+)
syntax. As for your problem, you can use IN
:
SELECT p.Name, a.Attribute
FROM People p
LEFT OUTER JOIN Attributes a
ON p.PersonID = a.PersonID AND a.Attribute IN ('Happy','Grouchy')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With