SELECT (SELECT user_id FROM users WHERE user_id=1) AS user_id, (SELECT usersetting_user_id FROM usersettings WHERE usersetting_user_id=1) AS usersetting_user_id;

this returns:

| user_id | usersetting_user_id |  
|       1 |                   1 |

but I want to display all users, not just one. So I run the following:

SELECT (SELECT user_id FROM users) AS user_id, (SELECT usersetting_user_id FROM usersettings) AS usersetting_user_id;``

and get:

ERROR 1242 (21000): Subquery returns more than 1 row

Any idea how to circumvent this?


You should use a join statement, like so:

SELECT u.user_id, us.usersetting_user_id
FROM users u
JOIN usersettings us ON u.user_id = us.usersetting_user_id

This assumes usersettings.usersetting_user_id is a reference to the primary key (user_id) of users. Specifics can be found in the manual.

  • Thanks, it works. Don't understand what's with the "u" and "us" but I'll read the manual. EDIT: so those are aliases, ok.
    – w00t
    May 30 '12 at 9:05
  • u and us are aliases for the tables users and usersettings
    – m0ntassar
    May 30 '12 at 9:20
  • Sorry about that, I should've noted that below the example. Aliases aren't required, you could just as well use "users.user_id" instead of "u.user_id" without them, but it leads to excessively long queries when you start adding more tables.
    – Tzarium
    May 30 '12 at 9:52

Assign aliases in FROM clause, then use alias.fieldname. Also group SELECT and FROM parts

SELECT user_id.user_id, usersetting_user_id.usersetting_user_id
FROM users AS user_id, usersettings AS usersetting_user_id;
  • 1
    aah ok, so those are aliases. thanks for the explanation
    – w00t
    May 30 '12 at 9:09

I don't recommend to use in queries like this Joins or multiple froms because you loading all tables at all and then filtering it. It's like using Having instead of Where. It's related on mysql-server version but you can compare it byself with EXPLAIN (or EXPLAIN EXTENDED).

In your case I recommend you to use one table in from directive (basic table, users for example), and load other data with inner queries with Where id=user_id.

# query with basic table only
FROM users AS u;
# extended query
  (SELECT usersetting_user_id
    FROM usersettings
    WHERE usersetting_user_id = u.user_id) AS usersetting_user_id
FROM users AS u;

P.s. If you know that your tables have the same rows count you can use the solution with Join by Tzarium but as INNER/LEFT/RIGHT JOIN instead of JOIN or OUTER JOIN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.