ref DC does not report all dc if test.tj_dfesttype_dft is not filled
This query works by isolating the left join at the end
SELECT
dis_identifiant AS DC,
dis_date_creation,
dis_date_suppression,
dif_dis_identifiant AS DF,
dis_commentaires,
dif_ouv_identifiant,
ouv_libelle,
dif_code AS DF_code,
dic_code AS DC_code,
dif_localisation,
dif_orientation,
tdf_libelle AS type_DF,
tdc_libelle AS type_DC,
sta_code
FROM
test.tg_dispositif_dis
JOIN test.t_dispositifcomptage_dic ON
dic_dis_identifiant = dis_identifiant
JOIN test.t_dispositiffranchissement_dif ON
dif_dis_identifiant = dic_dif_identifiant
JOIN test.t_ouvrage_ouv ON
dif_ouv_identifiant = ouv_identifiant
JOIN test.t_station_sta ON
ouv_sta_code = sta_code
JOIN ref.tr_typedc_tdc ON
dic_tdc_code = tdc_code
LEFT JOIN (
SELECT * FROM test.tj_dfesttype_dft
JOIN ref.tr_typedf_tdf ON
tdf_code = dft_tdf_code
WHERE
dft_rang = 1) sub ON
dif_dis_identifiant = dft_df_identifiant
ORDER BY
dis_identifiant;
Edited by cedricbriandgithub