Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
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;
share|improve this answer
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
SELECT
  user_id
FROM users AS u;
# extended query
SELECT
  user_id,
  (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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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