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 columns in a sub query and can't seem to get anywhere with joining for replacement.

My Table Data is:

CREATE TABLE `w7nwd_com_mtapp_product_engine` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `make` varchar(45) DEFAULT NULL,
  `model` varchar(45) DEFAULT NULL,
  `engine_size` varchar(15) DEFAULT NULL,
  `engine_type` enum('P','D') DEFAULT NULL,
  `engine_code` varchar(155) DEFAULT NULL,
  `year` varchar(15) DEFAULT NULL COMMENT 'This isn''t a standard year, this could be 09-12.',
  `bhp` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_w7nwd_com_mtapp_engine_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_engine_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product_partnumber` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) DEFAULT NULL,
  `partnumber` varchar(245) DEFAULT NULL,
  `override_price_new` int(11) DEFAULT NULL,
  `override_price_ex` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `partnumber_UNIQUE` (`partnumber`),
  KEY `fk_w7nwd_com_mtapp_product_alias_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_product_alias_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product_oem` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `oem` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_w7nwd_com_mtapp_product_oem_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_product_oem_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `w7nwd_com_mtapp_product` VALUES (1);

INSERT INTO `w7nwd_com_mtapp_product_partnumber` VALUES ('1', '1', '708638', NULL, NULL),('7', '1', '708639', NULL, NULL);

INSERT INTO `w7nwd_com_mtapp_product_oem` VALUES ('1', '1', '345474574545'),('2', '1', '453453454543');

INSERT INTO `w7nwd_com_mtapp_product_engine` VALUES ('1', '1', 'AUDI', 'A5', '2.2', 'D', NULL, '2012', '1400'),
('2', '1', 'FORD', 'C5', '2.2', 'D', NULL, '2012', '1400');

And I tried:

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

Here is a sql fiddle with details -- 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.

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 4 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.