CODEWITHSUNDEEP
mysqlsubset
s4m0k
s4m0k

Reputation: 13

MySQL: Query a subset of rows

Give the table below:

TABLE : USER_ASSETS

USER_ID | ASSET_ID
-------------------
1       |  1
-------------------
1       |  2
-------------------
1       |  3
-------------------
2       |  2
-------------------
2       |  3
-------------------

If I search for the USER_ID with ASSET_ID equals to 1 and 2, it should return USER_ID 1 as USER_ID 1 has ASSET_ID's 1 and 2.

If I pass ASSET_ID = 1 and 2 and 4, it should return 0 rows as there are no USER_ID's that have ASSET_ID 1, 2 and 4.

If I pass ASSET_ID 2 and 3 it should return USER_ID 1 and 2 as both of these USER_ID's has ASSET_ID 2 and 3.

I'm stuck right now as i couldn't find the correct query for my desired result.

I have tried this:

SELECT DISTINCT ID FROM USER_ASSETS WHERE ASSET_ID IN (1, 2);

But the result is wrong as it returns both USER_ID 1 and 2.

I have also tried:

SELECT DISTINCT ID FROM USER_ASSETS WHERE ASSET_ID = 1 AND ASSET_ID = 2

But this always return to 0 rows as the WHERE clause is executed to a single row at a time.

Upvotes: 0

Views: 458

Answers (2)

pala_
pala_

Reputation: 9010

You could try something like this:

select user_id
  from user_assets
  where asset_id = 1 or asset_id = 2 ...
  group by user_id
  having count(distinct asset_id) = (number of assets you are looking for)

demo here showing your required output.

the distinct isn't necessary if (user_id, asset_id) is a unique key

Upvotes: 2

user554538
user554538

Reputation:

WHERE ASSET_ID IN (1, 2) is the same as WHERE ASSET_ID = 1 OR ASSET_ID = 2, and both USER_ID 1 and USER_ID 2 have records with ASSET_ID = 2, so this WHERE clause will include both those rows.

WHERE ASSET_ID = 1 AND ASSET_ID = 2 will fail because there's no single row that has both 1 and 2 for ASSET_ID.

I think what you're asking is how to retrieve a USER_ID that has all of a set of ASSET_IDs. In that case you'll need to build a more complex query:

SELECT A1.USER_ID 
FROM USER_ASSETS AS A1 
INNER JOIN USER_ASSETS AS A2
ON A1.USER_ID = A2.USER_ID
WHERE A1.ASSET_ID = 1 AND A2.ASSET_ID = 2

This will return all USER_ID values that have both ASSET_ID 1 and ASSET_ID 2. You can add as many INNER JOIN clauses and additional An.ASSET_ID conditions to the WHERE clause as you need.

Upvotes: 1

Related Questions