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.
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
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
