PDA

View Full Version : Using union in a subquery?



coasters2k
08-10-2006, 06:57 PM
I'm trying to make a query that fetches messages that were created by one of your friends (friendships are stored in a separate table) and was thinking this query would possibly do the trick:

SELECT *
FROM public_messages
WHERE author_id IN
(
(SELECT friend_from FROM friendships WHERE friend_to=1 AND pending=0)
UNION
(SELECT from_to FROM friendships WHERE friend_from=1 AND pending=0)
)

Where the user's id is 1... Running this query gives an error of:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ( SELECT from_to FROM friendships WHERE friend_from = 1 AND pending = 0 ) ' at line 1

and of course it works fine without the union... Is such a thing possible in one query?

arnyinc
08-10-2006, 07:08 PM
This doesn't answer your question specifically, but you could do something like this:

SELECT *
FROM public_messages
WHERE author_id IN
(SELECT friend_from FROM friendships WHERE friend_to=1 AND pending=0)
OR author_id IN
(SELECT from_to FROM friendships WHERE friend_from=1 AND pending=0)

coasters2k
08-10-2006, 07:19 PM
Oh duh, thanks! That works perfectly... Seems so simple now.

GJay
08-10-2006, 07:19 PM
why not:


SELECT *
FROM public_messages
WHERE author_id IN
(
SELECT friend_from FROM friendships WHERE (friend_to=1 OR friend_from=1) AND pending=0
)

?

Fumigator
08-10-2006, 07:27 PM
Heh I was in the middle of suggesting that same solution when I noticed the two subselects are comparing different fields (friend_from and from_to).

arnyinc
08-10-2006, 10:11 PM
GJay's answer is even better. Due to the original request of using a UNION I was in the mode of using two different tables and didn't even realize both fields were in the same table.