SQL join showing all combinaisons-CodePudding

I want a "join" that gives me every category (cat) and every site (cat_sites) knowing if they are associated to each other or not

CREATE TABLE cat (
  cat varchar(34) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE cat_sites (
  cat varchar(34) CHARACTER SET utf8 DEFAULT NULL,
  site varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Having the following values:

INSERT INTO `cat` VALUES (''),('Autobus - Autocar'),('Camion / Tracteur de semi-remorque'),('Camionette'),('Motocycle'),('Remorque <3,5'),('Remorque >3,5'),('Tracteur'),('Tricycle / Quadricycle'),('VÃ©hicule spÃ©cial'),('Voiture');

INSERT INTO `cat_sites` VALUES ('','EA'),('','MA'),('','SW'),('Autobus - Autocar','BI'),('Autobus - Autocar','LI'),('Autobus - Autocar','SW'),('Camion / Tracteur de semi-remorque','BI'),('Camion / Tracteur de semi-remorque','EA'),('Camion / Tracteur de semi-remorque','LI'),('Camion / Tracteur de semi-remorque','MA'),('Camion / Tracteur de semi-remorque','SW'),('Camionette','BI'),('Camionette','EA'),('Camionette','LI'),('Camionette','MA'),('Camionette','SW'),('Motocycle','BI'),('Motocycle','EA'),('Motocycle','MA'),('Motocycle','SW'),('Remorque <3,5','BI'),('Remorque <3,5','EA'),('Remorque <3,5','LI'),('Remorque <3,5','MA'),('Remorque <3,5','SW'),('Remorque >3,5','BI'),('Remorque >3,5','EA'),('Remorque >3,5','LI'),('Remorque >3,5','SW'),('Tracteur','BI'),('Tracteur','EA'),('Tracteur','LI'),('Tracteur','MA'),('Tracteur','SW'),('Tricycle / Quadricycle','BI'),('Tricycle / Quadricycle','EA'),('Tricycle / Quadricycle','MA'),('Tricycle / Quadricycle','SW'),('VÃ©hicule spÃ©cial','BI'),('VÃ©hicule spÃ©cial','LI'),('Voiture','BI'),('Voiture','EA'),('Voiture','LI'),('Voiture','MA'),('Voiture','SW');

cat:

cat_sites:

The following query is almost exactly what I am looking for:

SELECT * FROM cat  LEFT JOIN cat_sites ON cat.cat = cat_sites.cat

The problem is, that I do get all categories, but only the sites actually assoicated to the given categories. For example "Autobus - Autocar" is only available in "BI", "LI" and "SW", but not in "EA" and "MA".

What I would like to see are records with c1="Autobus - Autocar" and the missing sites having c2 with null values.

I could get a result by passing by an intermediaire query which would give me all possible combinations:

SELECT cat.cat, site.site FROM cat 
RIGHT OUTER JOIN (select distinct site from cat_sites) AS site ON 1=1

The final query would then be something like this:

SELECT cat_allsites.cat c1, cat_allsites.site, cat_sites.cat c2
FROM
(
SELECT cat.cat, site.site FROM cat 
RIGHT OUTER JOIN (select distinct site from cat_sites) AS site ON 1=1
) as cat_allsites
LEFT JOIN cat_sites ON cat_allsites.cat = cat_sites.cat

WHERE cat_allsites.cat="Autobus - Autocar"
group by cat_allsites.cat , cat_allsites.site, cat_sites.cat 
;

Anyone have a better idea on how to do this?

CodePudding user response：

You would normally cross join two sets to find all possible combinations, then use a left join to find matching/missing values:

select cat.cat, sites.site, cat_sites.*
from cat
cross join (select distinct site from cat_sites) as sites
left join cat_sites on cat.cat = cat_sites.cat and sites.site = cat_sites.site