CODEWITHSUNDEEP
sqlpostgresqljoinself-join
pal
pal

Reputation: 105

converting a sub query into self join

I have this query in which i want to find who are all the other authors are for each title in which the author is Mr.X

The query I wrote for that is:

SELECT DISTINCT (author_name) as AUTHORS 
    from table1 
    where title = (Select title from table1 where (author_name) = 'X');

I got the ERROR: more than one row returned by a subquery used as an expression

I think to avoid this error i should use a self join but I'm not able to figure out how to do it.

Upvotes: 0

Views: 309

Answers (2)

Orlando Herrera
Orlando Herrera

Reputation: 3531

An extra answer, in my opinion the performance of your query improves using: "count"

SELECT DISTINCT (author_name) as AUTHORS 
from table1 t1
where ISNULL((Select COUNT(t2.title) from table1 t2 where (author_name) = 'X' AND t1.title = t2.title), 0) > 0

Upvotes: 0

Rahul
Rahul

Reputation: 77876

your subquery returning more than 1 record and in such case you can't use = operator rather You should use IN operator to check against multiple values like below 

where title in (Select title from table1 where (author_name) = 'X')

So, your query should look like

SELECT DISTINCT (author_name) as AUTHORS 
    from table1 
    where title in (Select title from table1 where (author_name) = 'X');

To change it to join instead

SELECT DISTINCT (t1.author_name) as AUTHORS 
    from table1 t1
    join table1 t2
    on t1.title = t2.title

Upvotes: 3

Related Questions