Professional Web Applications Themes

Problems with a subquery - MySQL

Hi, I have this query that works coorrectly ( SELECT DISTINCT t1.connected_id FROM connections as t1 WHERE ( t1.connectee_id = 1 ) ) UNION ( SELECT DISTINCT t2.connected_id FROM connections as t1, connections as t2 WHERE ( t1.connectee_id = 1 ) AND ( t1.connected_id = t2.connectee_id ) ) but when i use it as a subquery like this SELECT * FROM users WHERE id IN ( ( SELECT DISTINCT t1.connected_id FROM connections as t1 WHERE ( t1.connectee_id = 1 ) ) UNION ( SELECT DISTINCT t2.connected_id FROM connections as t1, connections as t2 WHERE ( t1.connectee_id = 1 ) AND ...

  1. #1

    Default Problems with a subquery

    Hi,

    I have this query that works coorrectly

    ( SELECT DISTINCT t1.connected_id FROM connections as t1 WHERE
    ( t1.connectee_id = 1 ) ) UNION ( SELECT DISTINCT t2.connected_id FROM
    connections as t1, connections as t2 WHERE ( t1.connectee_id = 1 ) AND
    ( t1.connected_id = t2.connectee_id ) )

    but when i use it as a subquery like this

    SELECT * FROM users WHERE id IN ( ( SELECT DISTINCT t1.connected_id
    FROM connections as t1 WHERE ( t1.connectee_id = 1 ) ) UNION ( SELECT
    DISTINCT t2.connected_id FROM connections as t1, connections as t2
    WHERE ( t1.connectee_id = 1 ) AND ( t1.connected_id =
    t2.connectee_id ) ) )

    i get a syntax error
    ERROR 1064 (42000): 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 DISTINCT t2.connected_id FROM
    connections as t1, connections as t' at line 1

    I don't understand what is the problem. How can i get this to work?
    Thanks
    f.mardini

    f.mardini Guest

  2. #2

    Default Re: Problems with a subquery

    On Apr 17, 2:46 pm, "f.mardini" <com> wrote: 

    Use LEFT JOINs instead of subqueries, they are sooooo much mrore
    efficient.

    Captain Guest

  3. #3

    Default Re: Problems with a subquery

     

    thanks, i wouldn't even know how to use them in this case, how can i
    specify the ON clause on the result of the UNION statement.
    Furthermore, i am actually interested to know why the above query
    fails

    thanks

    f.mardini Guest

  4. #4

    Default Re: Problems with a subquery

    I find it difficult to understand this query of yours
    (although, I'm learning SQL for only a few weeks so it must be it :)
    but explain this please:

    ( SELECT DISTINCT t2.connected_id
    *FROM connections as t1, connections as t2
    WHERE ( t1.connectee_id = 1 ) AND
    **( t1.connected_id = t2.connectee_id ) )

    *here, you are usig operator AS to make to tables (t1,t2) from table
    connection...
    ** and still you compare theirs connected_id-s

    aren't those, always, the same values?!
    those are copys, right?

    and in the first subquery...

    ( SELECT DISTINCT t1.connected_id
    FROM connections as t1
    WHERE
    ( t1.connectee_id = 1 ) )

    you are just looking for id-s that equal 1?

    I would find it much easyer (my-spelling-bad-here?) to make just one query.
    Something like:

    SELECT users.*
    FROM users JOIN connections
    ON id=connetee_id
    WHERE connectee_id = 1

    But again, I can't really tell what is it that you are exactly tryin to
    do...

    (sorry for replying on your email the first time)

    Duz Guest

  5. #5

    Default Re: Problems with a subquery

    On Apr 18, 1:25 am, "Duz" <hr> wrote: 

    OK, I am trying to achieve the following
    Table connections represents a network between users, connected_id and
    connectee_id are foreign keys from the users table. Also each link can
    be found twice in the connections table (with the connected and
    connecdtee reversed)
    I am basically trying to get all the connections that are at most
    separated by two degrees of separation of a specific user (with id 1
    in this case).
     

    here i am getting all the connections of those connected to id 1
     
    no problem :)

    f.mardini

    f.mardini Guest

  6. #6

    Default Re: Problems with a subquery

    well, i solved the issue by rewriting the query slightly differently,
    as follows

    SELECT id FROM users WHERE id IN ( SELECT DISTINCT t1.connected_id
    FROM connections as t1 WHERE ( t1.connectee_id = 1 ) )
    UNION
    SELECT id FROM users WHERE id IN ( SELECT DISTINCT t2.connected_id
    FROM connections as t1, connections as t2 WHERE ( t1.connectee_id =
    1 ) AND ( t1.connected_id = t2.connectee_id ) );

    i just can't figure out why the original query does not work.

    f.mardini Guest

Similar Threads

  1. Best way to use subquery?
    By greg.scharlemann@gmail.com in forum MySQL
    Replies: 4
    Last Post: November 27th, 01:25 AM
  2. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  3. Help with subquery
    By Nalini in forum Dreamweaver AppDev
    Replies: 3
    Last Post: August 15th, 12:07 PM
  4. How use Subquery better ???
    By lubiel in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 07:47 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139