...

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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum