Too many sub queries

[Suchy]

I have 2 tables, friends:

user | friend

-----------------

  1        2

  4        1

  4        2

  4        3

 

and posts:

post  |  user  |  time

-------------------------

aa        1          10:00

bb        2          10:01

cc          1          11:00

dd        3          12:00

 

anyway Im trying to get all the posts that a certain user and all of his friends have made. My query is:

SELECT post, user , time 
FROM posts 
WHERE user = (
                  SELECT friend FROM friends WHERE user = 1
                  UNION SELECT user FROM friends WHERE friend = 1
              )

 

But its not working, im getting: "Too many sub queries" error

 

[ebmigue]

SELECT post, user , time 
FROM posts 
WHERE user = (
                  SELECT friend FROM friends WHERE user = 1
                  UNION SELECT user FROM friends WHERE friend = 1
              )

 

The problem with that query, is that you are comparing a scalar value (the 'user' attribute) with a set (the subquery).

 

Unfortunately, in SQL, instead of notifying you of the true problem with some unambiguous description,

you are instead notified with a cryptic message (i.e., "too many subqueries").

 

-----

 

To fix that, you only need to change your method of comparison, like so:

SELECT post, user , time 
FROM posts 
WHERE user IN (
SELECT friend FROM friends WHERE user = 1
UNION 
SELECT user FROM friends WHERE friend = 1
)

IOW, change the '=' operator to the 'IN' operator, which is a set operator.

 

Hope it helps.

[fenway]

Though is could be re-written as a join, too, since large IN() clauses aren't optimal.