Select multiple columns in subquery

Question

I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement.

http://sqlfiddle.com/#!2/2481e/1

The link is to the schema with my current results, the issues is I only want one engine to come back and not duplicate all the rows.

Any help appreciated, Many thanks.

Answer 1

It seems like you want the following:

SELECT p.*,
  `e`.*, 
  GROUP_CONCAT(`n`.`partnumber`) as `partnumbers`,
  GROUP_CONCAT(`o`.`oem`) as `oems`
FROM `w7nwd_com_mtapp_product` AS `p`
LEFT JOIN `w7nwd_com_mtapp_product_engine` as `e`
  ON `e`.`product_id` = `p`.`id`
LEFT JOIN `w7nwd_com_mtapp_product_partnumber` AS `n`
  ON `p`.`id`=`n`.`product_id`
LEFT JOIN `w7nwd_com_mtapp_product_oem` AS `o`
  ON `p`.`id`=`o`.`product_id`
GROUP BY `p`.`id`

This replaces the subqueries with a LEFT JOIN on each table. Then to get the GROUP_CONCAT() to work, I added a GROUP BY p.id