SQL Join multiple rows

Question

I am looking for how to select in a JOIN only the line with the cheapest amount. Because the ON clause responds to multiple lines.

By default, MySQL takes the first line that it finds and I can not act on it.

SELECT g.id
     , g.name
     , p.description
     , x.annually 
  FROM tblproductgroups g
  JOIN tblproducts p 
    ON p.gid = g.id 
   AND p.hidden = 0
  JOIN tblpricing x
    ON x.relid = p.id 
 WHERE g.hidden = 0 
   AND g.id in (1,2,3) 
   AND x.type = 'product' 
 GROUP 
    BY g.id

I have to modify the JOIN of such tblpricing, but any comparison operation of the column "annually" gives me an error.

Edit: samples

CREATE TABLE `tblproductgroups` (
  `id` int(10) NOT NULL,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `hidden` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `tblproductgroups` (`id`, `name`, `hidden`) VALUES
(1, 'Hébergement WEB', 0),
(2, 'Serveurs virtuels KVM', 0),
(3, 'Serveurs dédiés Pro', 0),
(5, 'Colocation', 0);

CREATE TABLE `tblproducts` (
  `id` int(10) NOT NULL,
  `gid` int(10) NOT NULL,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `hidden` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `tblproducts` (`id`, `gid`, `name`, `description`, `hidden`) VALUES
(1, 1, 'Web Basic 2018', 'blablabla', 0),
(2, 1, 'Web Classic 2018', 'blablabla', 0),
(3, 1, 'Web Advanced 2018', 'blablabla', 0),
(5, 2, 'VPS Basic', 'blablabla', 0),
(6, 2, 'VPS Classic', 'blablabla', 0),
(7, 2, 'VPS Advanced', 'blablabla', 0),
(8, 3, 'SD-S 2018', 'blablabla', 0),
(9, 3, 'SD-L 2016', 'blablabla', 1),
(10, 3, 'SD-M 2018', 'blablabla', 0),
(11, 3, 'SD-XL 2018', 'blablabla', 0);

CREATE TABLE `tblpricing` (
  `id` int(10) NOT NULL,
  `type` enum('product','addon') COLLATE utf8_unicode_ci NOT NULL,
  `relid` int(10) NOT NULL,
  `annually` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `tblpricing` (`id`, `type`, `relid`, `annually`) VALUES
(1, 'product', 1, '30'),
(39, 'product', 2, '20'),
(40, 'product', 3, '10'),
(42, 'product', 5, '100'),
(43, 'product', 6, '50'),
(44, 'product', 7, '25'),
(45, 'product', 8, '2000'),
(46, 'product', 9, '1000'),
(47, 'product', 9, '500'),
(48, 'product', 10, '250');

Result of my query is:

1   Hébergement WEB     blablabla   30.00
2   Serveurs virtuels KVM   blablabla   100.00
3   Serveurs dédiés Pro     blablabla   2000.00

the correct result is:

1   Hébergement WEB     blablabla   10.00
2   Serveurs virtuels KVM   blablabla   25.00
3   Serveurs dédiés Pro     blablabla   250.00

which colums is use for the amount ?? add a proper data sample and the expected result .. — ScaisEdge, Feb 20 '18 at 13:20
@scaisEdge: I added the structure of the 3 tables, data, and the result that I have with my Query and the one I am waiting for. Sorry it took me a few minutes. Thank you. — Mickael, Feb 20 '18 at 13:48

score 1 · Answer 1 · answered Feb 20 '18 at 13:59

Crudely...

SELECT a.*
  FROM 
     ( SELECT g.id
            , g.name
            , p.description
            , x.annually 
         FROM tblproductgroups g
         JOIN tblproducts p 
           ON p.gid = g.id 
          AND p.hidden = 0
         JOIN tblpricing x
           ON x.relid = p.id 
        WHERE g.hidden = 0 
          AND g.id in (1,2,3) 
          AND x.type = 'product' 
     ) a
  JOIN
     ( SELECT id
            , MIN(annually) annually
         FROM 
            ( SELECT g.id
                   , g.name
                   , p.description
                   , x.annually 
                FROM tblproductgroups g
                JOIN tblproducts p 
                  ON p.gid = g.id 
                 AND p.hidden = 0
                JOIN tblpricing x
                  ON x.relid = p.id 
               WHERE g.hidden = 0 
                 AND g.id in (1,2,3) 
                 AND x.type = 'product' 
            ) x
        GROUP 
           BY id
    ) b
   ON b.id = a.id 
  AND b.annually = a.annually

indeed, it seems to me quite correct! nothing very complicated yet in theory, but with SQL .. a good example that shows the puzzle that becomes very quickly SQL for simple things. Thanks ! — Mickael, Feb 20 '18 at 15:02

ttzn · Answer 2 · 2018-02-20T14:22:53.410

This should do it :

SELECT g.id
     , g.name
     , p.name
     , x.annually 
FROM tblproductgroups g
JOIN tblproducts p 
  ON p.gid = g.id 
  AND p.hidden = 0
JOIN tblpricing x 
  ON x.relid = p.id
/* Subtable containing the minimum annual fee per group */
JOIN (SELECT subg.id, MIN(subx.annually) AS annually FROM tblproductgroups subg
      INNER JOIN tblproducts subp On subg.id = subp.gid
           AND subp.hidden = 0
      INNER JOIN tblpricing subx ON subx.relid = subp.id
      WHERE subg.hidden = 0 
        AND subg.id in (1,2,3) 
        AND subx.type = 'product'
      GROUP BY subg.id) m 
  ON g.id = m.id AND x.annually = m.annually
WHERE g.hidden = 0 
  AND g.id in (1,2,3) 
  AND x.type = 'product'

Don't use GROUP BY if you're not actually using any aggregation function in your column definitions. It might work in MySQL but the results will be unpredictable unless you know exactly what you're doing.

@Mickael This is more elegant than my solution – Strawberry Feb 20 '18 at 15:12 — Strawberry, Feb 20 '18 at 15:12

SQL Join multiple rows

2 Answers2