MySQL Select ID’s which occur on different rows with multiple specific values for a column

by

Your expression in a WHERE clause works against a single row of the joined result set. That’s why WHERE category_id = 201 AND category_id = 202 doesn’t work — because it can’t be two values on a single row.

So you need some way to join two rows from the table into one row of the result set. You can do this with a self-join:

SELECT c1.item_id
FROM item_category AS c1
INNER JOIN item_category AS c2 ON c1.item_id = c2.item_id
WHERE c1.category_id = 201 AND c2.category_id = 202

This technique is hard to scale up when you want to search for three, four, five or more values, because it requires N-1 joins to match N values.

So another method is to use GROUP BY:

SELECT c.item_id, COUNT(*) AS cat_count
FROM item_category AS c
WHERE c.category_id IN (201,202)
GROUP BY c.item_id
HAVING cat_count = 2

Both techniques are okay, and work better in different circumstances.

More Related Contents:

  1. How to filter SQL results in a has-many-through relation
  2. How to return rows that have the same column values in MySql
  3. Select values that meet different conditions on different rows?
  4. 1052: Column ‘id’ in field list is ambiguous
  5. How to add levenshtein function in mysql?
  6. Hierarchical queries in MySQL
  7. #1071 – Specified key was too long; max key length is 1000 bytes
  8. Is the primary key automatically indexed in MySQL?
  9. Is there ANY_VALUE capability for mysql 5.6?
  10. Cumulative sum over a set of rows in mysql
  11. MySql Error 150 – Foreign keys
  12. MySql: MyISAM vs. Inno DB! [closed]
  13. No connection could be made because the target machine actively refused it (PHP / WAMP)
  14. Add Auto-Increment ID to existing table?
  15. MySQL SELECT DISTINCT multiple columns
  16. DOs and DONTs for Indexes [closed]
  17. Using group by on two fields and count in SQL
  18. How do I get the first day of the week of a date in mysql?
  19. How to insert images from database?
  20. MySQL – ORDER BY values within IN()
  21. MySQL CONCAT returns NULL if any field contain NULL
  22. MySQL convert timediff output to day, hour, minute, second format
  23. How to format number with “.” as thousands separator and “,” as decimal separator?
  24. Order by FIELD in MYSQL
  25. Error installing mysqlclient for python on Ubuntu 18.04
  26. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  27. connecting to a docker-compose mysql container denies access but docker running same image does not
  28. MySQL’s lower_case_table_names won’t change
  29. mysql – move rows from one table to another
  30. #1214 – The used table type doesn’t support FULLTEXT indexes

Leave a Comment