Select multiple columns in subquery

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.

• Mr. Radical: could you show an example table with your desired output?

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

• Bradley Weston: I had this query to begin with the only problem using joins and 2 group concats makes the partnumbers and oems duplicated.
• Taryn: @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.
• Bradley Weston: 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.
• Taryn: @BradleyWeston is this what you are talking about -- sqlfiddle.com/#!2/2481e/6?
• Bradley Weston: I want 30 products listed just 1 engine per product.