Geek asks and answers

Workaround for externally joining an IN statement in Oracle

I use Oracle SQL, so external joins have good (+) syntax. I must warn you that I am not allowed to recycle the database; I work in a large organization.

Here are some sample 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 if we have the knowledge that they are happy or grouchy. Below is the output I want:

Name       Mood
Elmo       Happy
Oscar      Grouchy
Chris

So, here is a query that I was thinking of using:

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 like that.)

But actually I am not allowed to use OR inside an outer join at all! What should I do instead?

+3
source share
3 answers

, OUTER JOIN s?, Oracle (+). , 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')
+8

Oracel SQL "" Oracle, ...

SELECT p.Name,
       a.Attribute
  FROM people p,
       (SELECT PersonID,
               Attribute
          FROM attributes
              WHERE Attribute = 'Happy'
              OR Attribute = 'Grouchy') a
  WHERE p.personid = a.personid(+)

, ANSI Oracle - . Oracle , , // .

+1

, . ORA-01719, "" @Lamak, :

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

More articles:


All Articles