MySQL: Query a subset of rows

Question

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.

thanks for the edit @Dijkgraaf, I am thinking of using a join of the same table for every ASSET_ID value, but is it the correct approach? — s4m0k
– s4m0k, Commented May 25, 2015 at 3:06
no it isn't (well, it's a solution, but imo an unecessarily complicated one). see answer below. — pala_
– pala_, Commented May 25, 2015 at 3:08
See Misandrist's answer where he does exactly that, but it gets a bit unwieldy very fast. Pala_'s answer might also work. Try them out — Dijkgraaf
– Dijkgraaf, Commented May 25, 2015 at 3:10

pala_ · Accepted Answer · 2015-05-25 03:04:59Z

2

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

answered May 25, 2015 at 3:04

pala_

9,0101 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

s4m0k Over a year ago

Thank you, this is returning exactly the rows that i wanted.

score 1 · Accepted Answer · 2015-05-25 02:59:55Z

1

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.

edited May 25, 2015 at 2:59

answered May 25, 2015 at 2:52

user554538

2 Comments

s4m0k Over a year ago

Yes exactly, can you please tell me the correct approach? Thank you

s4m0k Over a year ago

Thanks for the answer, this JOIN is also what i thought about , but the ASSET_ID rows might be more than 10 and this means a join for the table 10x which will impact performance

Collectives™ on Stack Overflow

MySQL: Query a subset of rows

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related