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] => ...,
),
),
);