CODEWITHSUNDEEP
mysqlmysql-error-1242
Blaine
Blaine

Reputation: 1137

Selecting with subqueries in MySQL (Subqueries with ANY, and IN)

Thanks for the great answers!

For More Information

This is hard to explain, so lets set the stage...

userActions         userGroupMap
+------+--------+   +------+-------+
| user | action |   | user | group |
+------+--------+   +------+-------+
| x    | acted! |   | x    | a     |
| y    | acted! |   | y    | a     |
| y    | acted! |   | z    | b     |
| z    | acted! |   +------+-------+
| y    | acted! |
| z    | acted! |
| x    | acted! |
| z    | acted! |
+------+--------+

I want to select group a's actions. My idea was to

SELECT actions, user FROM userActions
    WHERE user = (SELECT user, group FROM userGroupMap WHERE group = a)

But obviously this subquery returns more than one row. Should I use a JOIN?

Subquery returns more than 1 row

Upvotes: 2

Views: 10652

Answers (6)

achinda99
achinda99

Reputation: 5078

One approach is this:

SELECT actions,
       user
FROM   userActions
WHERE  user IN
               (SELECT user
               FROM    userGroupMap
               WHERE   [group] = 'a'
               );

However, with large tables, this query tends to be inefficient and doing a join is better:

SELECT actions,
       userActions.user
FROM   userActions
       INNER JOIN
              (SELECT user
              FROM    userGroupMap
              WHERE   [group] = 'a'
              ) AS tmp
       ON     userActions.user = tmp.user;

Alternatively, as Jonathon mentioned, you could have done this and its pretty much as efficient, if not more:

SELECT actions,
       userActions.user
FROM   userActions
       INNER JOIN userGroupMap
       ON     userActions.user = userGroupMap.user
WHERE  [group] = 'a';

Upvotes: 3

qpingu
qpingu

Reputation: 960

SELECT actions, user FROM userActions
    WHERE user = (SELECT user FROM userGroupMap WHERE group = a)

The subquery was returning user and group (two fields) when it should be returning just user.

Upvotes: 0

snnkmtt
snnkmtt

Reputation: 11

Couldn't you just do something like:

SELECT 
  a.actions, 
  a.user 
FROM 
  userActions a 
  INNER JOIN userGroupMap g 
    ON a.user = g.user
WHERE
  g.group = 'a'

Upvotes: 1

Glavić
Glavić

Reputation: 43572

Rather use join than subquery:

SELECT
    userActions.action,
    userActions.user
FROM
    userActions
CROSS JOIN userGroupMap ON 
    userGroupMap.user = userActions.user AND
    userGroupMap.group = 'a'

Upvotes: 0

Nathan DeWitt
Nathan DeWitt

Reputation: 6601

Actually, this query will give you what you need:

SELECT actions, user 
FROM userActions 
WHERE user IN 
    (SELECT user FROM userGroupMap WHERE group = 'a')

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 754730

SELECT actions, user FROM userActions
    WHERE user IN (SELECT user FROM userGroupMap WHERE group = a)

SELECT actions, user FROM userActions
    WHERE user = ANY (SELECT user FROM userGroupMap WHERE group = a)

(Amended: only the user column should be returned, as noted by others.)

Upvotes: 1

Related Questions