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.
Best Answer
It seems like you want the following:
This replaces the subqueries with a
LEFT JOIN
on each table. Then to get theGROUP_CONCAT()
to work, I added aGROUP BY p.id