Im currently trying to combine 2 queries results in one. These 2 queries has the same number of rows and are group by the same field but has different column.
This works :
SELECT Distinct
MAX(d.libelle) AS libelle_dpt,
MAX(d.code_dpt) AS code_dep,
MAX(r.libelle) AS libelle_region,
MAX(pd.pop_dep) AS nb_habitants,MAX(ls.nb_canton) AS nb_canton
FROM election_2015.commune c
LEFT JOIN (
SELECT MAX(c.code_canton) AS code_canton,Count(distinct c.code_canton) AS nb_canton
FROM election_2015.commune co
JOIN election_2015.departement d
ON co.code_dpt = d.code_dpt
JOIN election_2015.canton c
ON c.code_canton = co.code_canton
GROUP BY d.code_dpt
ORDER BY d.code_dpt ASC
) ls
ON ls.code_canton = c.code_canton
JOIN election_2015.departement d
ON d.code_dpt = c.code_dpt
JOIN election_2015.region r
ON d.code_region = r.code_region
JOIN election.popgent_all pd
ON pd.dep = d.code_dpt
GROUP BY d.code_dpt
But I was wondering if there is an other way to do this, maybe like an union but with rows? Something like this (not working cause queries hasn't the same number of columns) :
SELECT Distinct
MAX(d.libelle) AS libelle_dpt,
MAX(d.id) AS id_dep,MAX(d.code_dpt) AS code_dep,
MAX(r.libelle) AS libelle_region,
MAX(pd.pop_dep) AS nb_habitants
FROM election_2015.commune c
LEFT JOIN election_2015.departement d
ON d.code_dpt = c.code_dpt
LEFT JOIN election_2015.region r
ON d.code_region = r.code_region
LEFT JOIN election.popgent_all pd
ON pd.dep = d.code_dpt
GROUP BY d.code_dpt
UNION
SELECT Count(distinct c.code_canton) AS nb_canton FROM election_2015.commune co
JOIN election_2015.departement d
ON co.code_dpt = d.code_dpt
JOIN election_2015.canton c
ON c.code_canton = co.code_canton
GROUP BY d.code_dpt
ORDER BY election_2015.departement.code_dpt ASC
Thanks for any help. Alexandre