Select multiple columns in subquery

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published.