CODEWITHSUNDEEP
mysqlsqljoinsubquery
Simon
Simon

Reputation: 189

Convert subquery to join

Is it possible to convert the following subquery to a JOIN, preferably without a derived table?

SELECT * FROM users u
LEFT JOIN user_groups ug ON u.usergroupid=ug.groupid
WHERE
u.userstatus=1 AND
ug.groupstatus=1 AND
ug.grouprank>=(SELECT grouprank FROM user_groups WHERE groupkey='users')

The user_groups table looks like:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| groupid     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| groupkey    | varchar(8)          | NO   |     | NULL    |                |
| grouprank   | smallint(6)         | NO   |     | NULL    |                |
| groupstatus | tinyint(1) unsigned | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

The users table looks like:

+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| userid       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username     | varchar(32)         | NO   |     | NULL    |                |
| userpassword | varchar(32)         | NO   |     | NULL    |                |
| usergroupid  | tinyint(3) unsigned | NO   |     | NULL    |                |
| userstatus   | tinyint(1) unsigned | NO   |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+

Upvotes: 2

Views: 2301

Answers (2)

ruakh
ruakh

Reputation: 183321

Assuming that exactly one user_groups record has groupkey = 'users' (since otherwise your query is invalid), your query is equivalent to this:

SELECT u.*,
       ug1.*
  FROM users u
  LEFT
  JOIN user_groups ug1
    ON u.usergroupid = ug1.groupid
  LEFT
  JOIN user_groups ug2
    ON ug2.groupkey = 'users' -- not a real join condition
 WHERE u.userstatus = 1
   AND ug1.groupstatus = 1
   AND ug1.grouprank >= ug2.grouprank
;

But note that the LEFT JOINs actually end up working as INNER JOINs, since your WHERE clause depends on the joins having succeeded. So you probably really want something like this:

SELECT u.*,
       ug1.*
  FROM users u
  LEFT
  JOIN user_groups ug1
    ON u.usergroupid = ug1.groupid
   AND ug1.groupstatus = 1
  LEFT
  JOIN user_groups ug2
    ON ug2.groupkey = 'users'
   AND ug1.grouprank >= ug2.grouprank
 WHERE u.userstatus = 1
;

Upvotes: 2

ean5533
ean5533

Reputation: 8994

I think this is what you're looking for -- give it a test.

SELECT * FROM users u
LEFT JOIN user_groups ug ON u.usergroupid=ug.groupid
JOIN user_groups ug2 ON ug.grouprank >= ug2.grouprank AND ug2.groupkey = 'users'
WHERE
u.userstatus=1 AND
ug.groupstatus=1

Upvotes: 1

Related Questions