1

I have these tables

select * from mass_categories;
+----+--------------------------+
| id | categorie                |
+----+--------------------------+
|  1 | ESI-QTOF                 |
|  2 | MALDI-TOF                |
|  3 | FT-MS                    |
|  4 | GC-MS                    |
|  5 | IT-MS                    |
|  6 | ICP                      |
|  7 | Standard MS Analysis     |
|  8 | Analysis Scientist Cost  |
|  9 | Analysis Technician Cost |
+----+--------------------------+

select * from mass_groups;
+----+-----------+------------------+
| id | groupname | groupdisplayname |
+----+-----------+------------------+
|  2 | LOCAL     | LOCAL (U1)       |
|  4 | ACADEMIC  | ACADEMIC (U2)    |
|  5 | EXTERNAL  | EXTERNAL (U3)    |
+----+-----------+------------------+

and "mass_prices" which is empty but with this structure

CREATE TABLE `mass_prices` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `id_categorie` smallint(5) unsigned NOT NULL,
  `hour_price` float(6,2) DEFAULT NULL,
  `id_group` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_categorie` (`id_categorie`,`id_group`),
  KEY `id_group` (`id_group`)

I could launch this select which returns me what I want when the mass_prices is empty

select categorie, mg.id as groupid, hour_price
    from mass_categories mc, mass_groups mg
    LEFT JOIN mass_prices mp on mp.id_group=mg.id;
+--------------------------+---------+------------+
| categorie                | groupid | hour_price |
+--------------------------+---------+------------+
| ESI-QTOF                 |       2 |       NULL |
| ESI-QTOF                 |       4 |       NULL |
| ESI-QTOF                 |       5 |       NULL |
| MALDI-TOF                |       2 |       NULL |
| MALDI-TOF                |       4 |       NULL |
| MALDI-TOF                |       5 |       NULL |
| FT-MS                    |       2 |       NULL |
| FT-MS                    |       4 |       NULL |
| FT-MS                    |       5 |       NULL |
| GC-MS                    |       2 |       NULL |
| GC-MS                    |       4 |       NULL |
| GC-MS                    |       5 |       NULL |
| IT-MS                    |       2 |       NULL |
| IT-MS                    |       4 |       NULL |
| IT-MS                    |       5 |       NULL |
| ICP                      |       2 |       NULL |
| ICP                      |       4 |       NULL |
| ICP                      |       5 |       NULL |
| Standard MS Analysis     |       2 |       NULL |
| Standard MS Analysis     |       4 |       NULL |
| Standard MS Analysis     |       5 |       NULL |
| Analysis Scientist Cost  |       2 |       NULL |
| Analysis Scientist Cost  |       4 |       NULL |
| Analysis Scientist Cost  |       5 |       NULL |
| Analysis Technician Cost |       2 |       NULL |
| Analysis Technician Cost |       4 |       NULL |
| Analysis Technician Cost |       5 |       NULL |
+--------------------------+---------+------------+

but as soon as I add lines in this mass_prices, this select query is returning too many lines.

So to resume, I need a select which takes each "mass_groups" then join each "mass_categories" and mass_prices even if prices are not defined.

I'll use that on a webpage to allow users to change those prices.

Thanks !

  • Use DECIMAL, not FLOAT for currency. – Rick James May 8 '19 at 0:31
  • Will change that, thanks for the tip! – darxmurf May 8 '19 at 5:43
2

You first have an (implied) CROSS JOIN between mass_categories and mass_groups, then LEFT JOIN to mass_prices. But the left join is using the id (group_id) only from one of the two tables for the join condition:

FROM 
    mass_categories mc, mass_groups mg   -- the CROSS join
    LEFT JOIN mass_prices mp
      ON  mp.id_group = mg.id             -- LEFT join condition 

Use both the group and the category id (and an explicit CROSS JOIN to not get a different error):

FROM 
    mass_categories AS mc
    CROSS JOIN                         -- doesn't byte 
    mass_groups AS mg
    LEFT JOIN mass_prices AS mp
      ON  mp.id_group = mg.id          -- LEFT join condition
      AND mp.id_categorie = mc.id 
;

note:

You could still use the (implicit CROSS join) comma operator if you want but that would just make your life difficult. The two operators have different precedence, so you'd need to add parentheses as well:

FROM 
    ( mass_categories AS mc , mass_groups AS mg )
    LEFT JOIN mass_prices AS mp
      ON  mp.id_group = mg.id          -- LEFT join condition
      AND mp.id_categorie = mc.id 
;
| improve this answer | |
  • Damn, I did not know about CROSS JOIN, thanks ! I first tried to add this AND mp.id_categorie = mc.id to my first query but it was returning an error. – darxmurf May 7 '19 at 6:54
  • The , you had was doing a cross join, too. The error you got when adding the AND was because , and CROSS JOIN operators have different precedence. – ypercubeᵀᴹ May 7 '19 at 7:53

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.