MySQL, join one item to multiple items in a single query
Say I've got two tables. One contains a list of items, and one contains pictures of the items.
Something like this:
Items
+----+---------+
| id | name |
+----+---------+
| 1 | oranges |
| 2 | apples |
+----+---------+
Pictures
+----+---------+
| id | item_id |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
+----+---------+
My question is this: is it possible in a single query to select item 2
from Items
and join all the rows in the Pictures
table whose item_id
references that same id?
I am sure I've tried almost everything I can think of, but nothing is coming to mind. I apologize if the answer to this is simple, but It's just escaping me right now.
EDIT
@Andomar, that works, but it doesn't exactly give me the results I wanted. I guess I should be more clear and ask is it possible to get results that resemble something like this (it's a php array, but the organization is what I'm after):
$results = array(
[0] => array(
'id' => 1,
'name' => 'oranges',
'images' => array(
[0] => ...,
),
),
[1] => array(
'id' => 2,
'name' 开发者_StackOverflow中文版=> 'apples',
'images' => array(
[0] => ...,
[1] => ...,
),
),
);
A join should do the trick:
select *
from Items i
left join Pictures p on p.item_id = i.id
where i.id = 2
SELECT i.name, GROUP_CONCAT(p.id SEPARATOR ',') AS pictures
FROM Items i
LEFT JOIN Pictures p
ON p.item_id = i.id
WHERE i.id = 2
Without GROUP_CONCAT it'll return a different row for each matched Picture. By grouping the results from the Pictures
table you can get a single row as a result.
精彩评论