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

On Apr 17, 2:46 pm, "f.mardini" <f.mard...@gmail.com> wrote:
> 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

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

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

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

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)

On Apr 18, 1:25 am, "Duz" <jakov.duze...@fer.hr> wrote:
> 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:
>

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

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

here i am getting all the connections of those connected to id 1

> 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)
no problem :)

f.mardini

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.