开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜