MYSQL Get results assigned to multiple "categories" maybe using JOIN?
categories, join, mysql, php, tags
Solution
Try something like this:
SELECT * FROM Results r
INNER JOIN Categories c on r.ID = c.RESULT_ID
WHERE c.name in ('purchase', 'single_family', 'conventional')
GROUP BY r.ID
HAVING COUNT(c.ID) = 3
The basic select with join will get you three rows only for result 1.
Edit: To make sure your code won't break if you change your database you should always select the fields you want explicitly: `SELECT r.ID, .. FROM ..`
So you're basically doing a simple join with all the category table for all categories where the category name is one of the names in the list. Try to run the 3 first lines manually to see the result you get.
Next you group by the result id. This means that you are aggregating all the rows sharing the same result id into one row. The last line means that we are filtering the aggregated columns that are aggregated by 3 rows. That means that you will only return results that have 3 matching categories.
So the only problem with this approach is if you have duplicate result_id, categoryname in your Categories table.
Problem
I am new to the world of mysql and I'm having some trouble getting the data I need from a database. The 2 tables I have are... Results ``` ID | TITLE | LOTS OF OTHER DATA | 1 | res1 | | 2 | res2 | | 3 | res3 | | 4 | res4 | | 5 | res5 | | ``` Categories ``` ID | RESULT_ID | CATEGORY NAME | 1 | 1 | purchase | 2 | 1 | single_family | 3 | 1 | conventional | 4 | 2 | usda | 5 | 3 | somecategory | ``` I'm trying to create a query that will select results that belong to all of the categories provided in the query. For example a query for purchase & single_family & conventional in this example would return the first result in the results table. Does that make sense? Is there a query that will do this or is this more of a problem with my database structure? Thanks a lot!