Tutorial :MySQL, join one item to multiple items in a single query



Question:

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


Solution:1

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.


Solution:2

A join should do the trick:

select *  from Items i  left join Pictures p on p.item_id = i.id  where i.id = 2  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »