MySql find data in one table based on conditions of two other tables
Help me out with this query: I have 3 tables with this structure.
items_to_groups (item_id | group_id)
item_to_regions (item_id | region_id)
开发者_StackOverflowitems [a bunch of columns]
I need to select every row on the item table that has an item_id match on item_to_groups table WHERE group = x AND has an item_id match on item_to_regions table WHERE region = y
Currently the code I have is a horrible subquery with loops and all.
What would be a better way of doing this? I've thought about JOIN and such, but can't really get my head around on how to do it.
SELECT bunch_of_columns
FROM items i
INNER JOIN items_to_groups ig ON i.id=ig.item_id
INNER JOIN items_to_regions ir on i.id=ir.item_d
WHERE ir.region_id=y
AND ig.group_id=x
Have a look at the JOIN documentation on MySQL. Joins are important for relational databases. As you said you have a hard time grasping joins, have a look at A Visual Explanation of SQL Joins by Jeff Atwood. Maybe it helps.
SELECT colums
FROM items
INNER JOIN items_to_groups ON items.item_id = items_to_groups.item_id AND group_id = x
INNER JOIN items_to_regions ON items.item_id = items_to_regions.item_id AND region_id = y
SELECT * FROM items
JOIN items_to_groups ON (items.item_id = items_to_groups.item_id AND group_id = ?)
JOIN items_to_regions ON (items.item_id = items_to_regions.item_id AND region_id = ?)
GROUP BY items.item_id
精彩评论