Subquery into join issue

All we need is an easy explanation of the problem, so here it is.

I’m trying hard as I can to transform my query to get rid of subqueries. I know its better to not use subqueries, but my first reason to change this query is because my ORM (Doctrine) isn’t able to use any join with subquery, it doesn’t support it (or CTEs).

Is there a way to get rid of subqueries in this query?

SELECT 
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name AS partner,
    p.id AS partnerId,
    contacts.names AS contactNames,
    contacts.tels AS contactTels,
    contacts.emails AS contactEmails
FROM
    service s
        JOIN
    web w ON s.web_id = w.id
        JOIN
    rus r ON w.rus_id = r.id
        JOIN
    partner p ON r.partner_id = p.id
        LEFT JOIN
    exception e ON e.service_id = s.id
        LEFT JOIN
    (SELECT 
        p.id,
            GROUP_CONCAT(c.name) names,
            GROUP_CONCAT(c.tel) tels,
            GROUP_CONCAT(c.email) emails
    FROM
        partner p
    LEFT JOIN contact c ON c.partner_id = p.id
    WHERE
        c.main = 1 OR c.important = 1
    GROUP BY p.id) contacts ON contacts.id = p.id
        LEFT JOIN
    contact c ON c.partner_id = p.id

Tables and sample data:

CREATE TABLE `partner` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ico` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `partner_idx_active` (`active`),
  FULLTEXT KEY `partnerEntity` (`name`,`ico`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `partner` (`id`, `name`, `ico`, `created`, `active`) VALUES
(1, 'partner1', '123',  '2021-05-18 22:27:24',  1);

CREATE TABLE `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tel` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  `main` int(11) DEFAULT NULL,
  `important` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_4C62E6389393F8FE` (`partner_id`),
  FULLTEXT KEY `contactEntity` (`name`,`email`,`tel`),
  CONSTRAINT `FK_4C62E6389393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `contact` (`id`, `partner_id`, `name`, `email`, `tel`, `created`, `active`, `main`, `important`) VALUES
(1, 1,  'contact1', '[email protected]',    '123456789',    '2021-05-18 22:28:30',  1,  1,  NULL),
(2, 1,  'contact2', '[email protected]',   '123456788',    '2021-05-18 22:28:48',  1,  NULL,   1),
(3, 1,  'contact3', '[email protected]',   '123451234',    '2021-05-18 22:29:13',  1,  NULL,   NULL);


CREATE TABLE `rus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_3370C8199393F8FE` (`partner_id`),
  KEY `rus_idx_active` (`active`),
  FULLTEXT KEY `rusEntity` (`email`),
  CONSTRAINT `FK_3370C8199393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `rus` (`id`, `partner_id`, `email`, `created`, `active`) VALUES
(1, 1,  '[email protected]',    '2021-05-18 22:27:36',  1);

CREATE TABLE `service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `web_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_E19D9AD2FE18474D` (`web_id`),
  KEY `service_idx_active` (`active`),
  FULLTEXT KEY `serviceEntity` (`name`),
  CONSTRAINT `FK_E19D9AD2FE18474D` FOREIGN KEY (`web_id`) REFERENCES `web` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `service` (`id`, `web_id`, `name`, `created`, `active`) VALUES
(1, 1,  'service1', '2021-05-18 22:28:08',  1);

CREATE TABLE `exception` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) DEFAULT NULL,
  `exception` longtext COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_7FC98E6D9393F8FE` (`partner_id`),
  KEY `FK_7FC98E6DED5CA9E6` (`service_id`),
  CONSTRAINT `FK_7FC98E6D9393F8FE` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`),
  CONSTRAINT `FK_7FC98E6DED5CA9E6` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `exception` (`id`, `partner_id`, `exception`, `created`, `service_id`) VALUES
(1, 1,  'test..',   '2021-05-18 22:31:14',  1);

CREATE TABLE `web` (
  `suspended` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rus_id` int(11) DEFAULT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `web_id` int(5) unsigned zerofill DEFAULT NULL,
  `created` datetime NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_15C9385126907186` (`rus_id`),
  KEY `web_idx_active` (`active`),
  FULLTEXT KEY `webEntity` (`url`),
  CONSTRAINT `FK_15C9385126907186` FOREIGN KEY (`rus_id`) REFERENCES `rus` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `web` (`suspended`, `id`, `rus_id`, `url`, `web_id`, `created`, `active`) VALUES
(0, 1,  1,  'www.test.com', 01337,  '2021-05-18 22:27:54',  1);

dbfiddle

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can just place all the other columns in the GROUP BY. It’s more verbose, but it does the job.

  • You should place all non-aggregated columns from the SELECT into the GROUP BY also. Although MariaDB and MySQL allow other columns in the SELECT, it is effectively an ANY(...) aggregate, and is not deterministic in many cases.
  • You need to pre-filter contact in the ON clause now, do not filter in the WHERE
  • It’s unclear why you have extra partner and contact lookups, they seems to serve no purpose.
SELECT 
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name AS partner,
    p.id AS partnerId,
    GROUP_CONCAT(c.name) AS contactNames,
    GROUP_CONCAT(c.tel) AS contactTels,
    GROUP_CONCAT(c.email) AS contactEmails
FROM
    service s
        JOIN
    web w ON s.web_id = w.id
        JOIN
    rus r ON w.rus_id = r.id
        JOIN
    partner p ON r.partner_id = p.id
        LEFT JOIN
    exception e ON e.service_id = s.id
        LEFT JOIN
    contact c ON c.partner_id = p.id AND
        (c.main = 1 OR c.important = 1)
GROUP BY
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name,
    p.id;

Method 2

What if you create a View for your subquery:

CREATE VIEW PartnerContact AS

SELECT 
    p.id,
        GROUP_CONCAT(c.name) names,
        GROUP_CONCAT(c.tel) tels,
        GROUP_CONCAT(c.email) emails
FROM
    partner p
LEFT JOIN contact c ON c.partner_id = p.id
WHERE
    c.main = 1 OR c.important = 1
GROUP BY p.id

…and then join to that View in your main query:

SELECT 
    s.id,
    e.exception,
    s.name,
    w.url,
    w.web_id,
    w.active,
    w.suspended,
    r.email,
    p.name AS partner,
    p.id AS partnerId,
    contacts.names AS contactNames,
    contacts.tels AS contactTels,
    contacts.emails AS contactEmails
FROM
    service s
        JOIN
    web w ON s.web_id = w.id
        JOIN
    rus r ON w.rus_id = r.id
        JOIN
    partner p ON r.partner_id = p.id
        LEFT JOIN
    exception e ON e.service_id = s.id
        LEFT JOIN
    PartnerContacts contacts ON contacts.id = p.id
        LEFT JOIN
    contact c ON c.partner_id = p.id

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply