tags:

views:

39

answers:

2

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'  => 'apples',
        'images' => array(
            [0] => ...,
            [1] => ...,
        ), 
    ),
);
+2  A: 

A join should do the trick:

select *
from Items i
left join Pictures p on p.item_id = i.id
where i.id = 2
Andomar
Thanks for the quick reply. It works great... but I guess I should have been more clear about what the desired results. I edited the question and would really appreciate any other input you might have.
bschaeffer
+1  A: 
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.

Alec
I edited the question, but your query seems to be really close to what I was looking for. This isn't really part of the original question, but I was wondering if there was a way to do something inside the `GROUP_CONCAT` statement like this `(p.name + "." + p.extension)` to end up with a result like `img1.png,img2.png`?
bschaeffer
You can use `CONCAT` for that, e.g.: `GROUP_CONCAT(CONCAT('img', p.id, '.png') SEPARATOR ',')`.
Alec