2

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.

  • 1
    could you show an example table with your desired output? – Mr. Radical Jan 24 '13 at 11:00
4

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

  • @RolandoMySQLDBA thanks, it was interesting. :) – Taryn 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. – Taryn 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
  • @BradleyWeston is this what you are talking about -- sqlfiddle.com/#!2/2481e/6? – Taryn Jan 23 '13 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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