Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
could you show an example table with your desired output? –  Mr. Radical Jan 24 '13 at 11:00

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
1  
+1 for bravely unscrambling this !!! –  RolandoMySQLDBA Jan 23 '13 at 19:15
    
@RolandoMySQLDBA thanks, it was interesting. :) –  bluefeet Jan 23 '13 at 19:19
    
I had this query to begin with the only problem using joins and 2 group concats makes the partnumbers and oems duplicated. –  Bradley Weston Jan 23 '13 at 20:17
    
@BradleyWeston you can always try GROUP_CONCAT(DISTINCT...). The other suggestion that I have if that does not work would be to edit your OP with your table structure, sample data and the desired result. Or even create a sql fiddle with your table data. –  bluefeet Jan 23 '13 at 20:19
    
This is what I have: sqlfiddle.com/#!2/2481e/1 But I don't want a new row for every engine I just want the first engine. –  Bradley Weston Jan 23 '13 at 20:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.