MySQL Join with empty table 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 !
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 
;