0
                          Tables
 __________                     
|__users__|                     
|_id_|    |                     
| A  |    |                     
| B  |    |     
| C  |    |     
 ---------     


___________________________
|___________owns__________|
|___userId___|___itemId___|
| A          | 1          |
| A          | 2          |
| B          | 1          |
| C          | 2          |
| C          | 3          |
 -------------------------

How do I select the users who don't own item 1. In this case, it should be user C.

2 Answers 2

0

One way would be to use LEFT JOIN as follows:

SELECT id 
FROM users 
LEFT JOIN 
    (SELECT userId from owns WHERE itemId=1)x ON id=userId 
WHERE userId IS NULL;

Example:

mysql> SELECT * FROM users;
+------+
| id   |
+------+
| A    |
| B    |
| C    |
+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM owns;
+--------+--------+
| userId | itemId |
+--------+--------+
| A      |      1 |
| A      |      2 |
| B      |      1 |
| C      |      2 |
| C      |      3 |
+--------+--------+
5 rows in set (0.00 sec)

# Result of the subquery
mysql> SELECT userId from owns WHERE itemId=1;
+--------+
| userId |
+--------+
| A      |
| B      |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT id FROM users LEFT JOIN (SELECT userId from owns WHERE itemId=1)x ON id=userId WHERE userId IS NULL;
+------+
| id   |
+------+
| C    |
+------+
1 row in set (0.00 sec)
1

That's typical query, that can be implemented with EXISTS() function:

SELECT
    *                           -- get fields
FROM
    users as u                  -- from users
WHERE
    NOT EXISTS(                 -- where there is no
        SELECT
            TRUE
        FROM
            owns as o           -- owns
        WHERE
            (o.userId = u.id)   -- with this user
            AND
            (o.itemId <> 1)     -- and such item
    );

For multiple items check you can use IN() in condition check of correlated subquery:

SELECT
    *                             -- get fields
FROM
    users as u                    -- from users
WHERE
    NOT EXISTS(                   -- where there is no
        SELECT
            TRUE
        FROM
            owns as o             -- owns
        WHERE
            (o.userId = u.id)     -- with this user
            AND
            (o.itemId IN (1, 3))  -- and items from subset
    );
2
  • Would the subquery work? I see that there's no join between the users table and owns table. Oct 13, 2016 at 15:58
  • Yes, it would work, because there is correlated condition, that acts like a JOIN you mentioned: (o.userId = u.id). I mean, owns.userId = users.id.
    – BlitZ
    Oct 13, 2016 at 15:59

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