Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Jun 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using union in a subquery?

    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?

  • #2
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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)

  • #3
    New Coder
    Join Date
    Jun 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh duh, thanks! That works perfectly... Seems so simple now.

  • #4
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    why not:
    Code:
    SELECT * 
    FROM public_messages 
    WHERE author_id IN
    (
    SELECT friend_from FROM friendships WHERE (friend_to=1 OR friend_from=1) AND pending=0
    )
    ?

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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).

  • #6
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •