Home / server / Questions / 393863
Accepted
w00t
Asked: 2012-05-31 00:52:57 +0800 CST

Multiple mysql 'select from' in one query

  • 772

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?

mysql

3 Answers

  1. Best Answer

    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.

    • 5

  2. 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;
    • 2

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

    • 0