CODEWITHSUNDEEP
sqloracle-database
bjar-bjar
bjar-bjar

Reputation: 707

Value in one OR another column

The following code is just a simplified description, due to the dataset, the structure of the query has to be this way.

I want to find entries where a value could exist in two columns of a sub-query.

This is what I have now:

SELECT * FROM PEOPLE WHERE RELATION = 'child' 
and (
    'John' IN (SELECT FIRSTNAME FROM PEOPLE WHERE AGE = 20 AND RELATION = 'parent' ) OR 
    'John' IN (SELECT ALIAS FROM PEOPLE WHERE AGE = 20 AND RELATION = 'parent')
) ;

I have found out that the OR gives really bad performance.

What I want to achieve is something like:

SELECT * FROM PEOPLE WHERE RELATION = 'child' AND 'John' IN 
(SELECT FIRSTNAME, ALIAS FROM PEOPLE WHERE AGE = 20 AND RELATION = 'parent');

The firstname and alias in this example is not necessarily the same value, so I cannot use:

SELECT * FROM PEOPLE WHERE RELATION = 'child' AND ('John','John') IN 
(SELECT FIRSTNAME, ALIAS FROM PEOPLE WHERE AGE = 20 AND RELATION = 'parent');

Since this will only find entires in which FIRSTNAME = ALIAS = 'John'.

Is there a better way to do it, than how it's done now?

Upvotes: 2

Views: 44

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31686

May be something like this?

SELECT * 
FROM   people 
WHERE  relation = 'child' 
       AND EXISTS (SELECT 1 
                   FROM   people 
                   WHERE  age = 20 
                          AND relation = 'parent' 
                          AND ( firstname = 'john' 
                                 OR alias = 'john' )) ;

Upvotes: 2

Related Questions