Mysql - 关联表 - on子句中的未知列

Question

The following query always returns #1054 - Unknown column 'T_RELATIF_SESS.REL_COM_ID' in 'on clause'

SELECT
`T_FORMATIONS`.`FOR_ID`, 
`T_FORMATIONS`.`FOR_TITRE`, 
`T_FORMATIONS`.`FOR_NIVEAU`, 
`T_FORMATIONS`.`FOR_MAX_PART`, 
`T_RELATIF_SESS`.`REL_COM_ID`, 
`T_RELATIF_SESS`.`REL_SES_ID`,
`T_SESSIONS`.`SES_ID`, 
`T_SESSIONS`.`SES_TITRE`, 
`T_SESSIONS`.`SES_TYPE`, 
`T_SESSIONS`.`SES_ADRESSE`, 
`T_SESSIONS`.`SES_NPA`, 
`T_SESSIONS`.`SES_LIEU`, 
`T_SESSIONS`.`SES_PRIX_SPECIAL`, 
`T_SESSIONS`.`SES_VAL_PRIX_SPECIAL`, 
`T_SESSIONS`.`SES_PRIX_SPEC_EXP`, 
`T_SESSIONS`.`SES_SUPP_COURS_INCL`, 
`T_SESSIONS`.`SES_SUPP_COURS_ADD`, 
`T_SESSIONS`.`SES_PRIX_SUPP_COURS_ADD`, 
`T_SESSIONS`.`SES_HORAIRE_SPECIAL`, 
`T_SESSIONS`.`SES_REMARQUES`, 
`T_SESSIONS`.`SES_REC_ID`, 
`T_COURS`.`COU_ID`, 
`T_COURS`.`COU_DATE`, 
    (SELECT `COU_DATE` FROM `T_COURS` WHERE `T_COURS`.`COU_SES_ID` = `T_SESSIONS`.`SES_ID` ORDER BY `COU_DATE` ASC LIMIT 1) AS `PREMIERCOURS`, 
    (SELECT `COU_DATE` FROM `T_COURS` WHERE `T_COURS`.`COU_SES_ID` = `T_SESSIONS`.`SES_ID` ORDER BY `COU_DATE` DESC LIMIT 1) AS `DERNIERCOURS` 
FROM `T_COURS` 
INNER JOIN `T_COMMANDES` ON `T_RELATIF_SESS`.`REL_COM_ID` = `T_COMMANDES`.`COM_ID` 
INNER JOIN `T_RELATIF_SESS` ON `T_SESSIONS`.`SES_ID` = `T_RELATIF_SESS`.`REL_SES_ID` 
INNER JOIN `T_SESSIONS` ON `T_COURS`.`COU_SES_ID` = `T_SESSIONS`.`SES_ID` 
INNER JOIN `T_FORMATIONS` ON `T_SESSIONS`.`SES_FOR_ID` = `T_FORMATIONS`.`FOR_ID` 
WHERE `T_COMMANDES`.`COM_ID`=19

This query worked fine before I add

INNER JOIN `T_COMMANDES` ON `T_RELATIF_SESS`.`REL_COM_ID` = `T_COMMANDES`.`COM_ID` 
INNER JOIN `T_RELATIF_SESS` ON `T_SESSIONS`.`SES_ID` = `T_RELATIF_SESS`.`REL_SES_ID` 

And

WHERE `T_COMMANDES`.`COM_ID`=19

T_RELATIF_SESS is juste an associative table between the tables called "T_SESSIONS" and "T_COMMANDES". I've tried to rewrite the query many times but I still get this error and I really don't understand why. Every field exists in my database.

I know it may be simple but it really gives me headache. Can someone can give me a hand or just explain to me what I do wrong? It would be much apprecietaded! Thank you very much!

Answer 1

So from your description you try to run the script without

INNER JOIN `T_COMMANDES` ON `T_RELATIF_SESS`.`REL_COM_ID` = `T_COMMANDES`.`COM_ID` 
INNER JOIN `T_RELATIF_SESS` ON `T_SESSIONS`.`SES_ID` = `T_RELATIF_SESS`.`REL_SES_ID` 

and it is working, but not with that script? Try to make it in order, since on your first inner join, you are trying to join the table (T_COMMANDES) with the table that you will join next (T_RELATIF_SESS).

So try:

INNER JOIN `T_SESSIONS` ON `T_COURS`.`COU_SES_ID` = `T_SESSIONS`.`SES_ID` 
INNER JOIN `T_FORMATIONS` ON `T_SESSIONS`.`SES_FOR_ID` = `T_FORMATIONS`.`FOR_ID` 
INNER JOIN `T_RELATIF_SESS` ON `T_SESSIONS`.`SES_ID` = `T_RELATIF_SESS`.`REL_SES_ID` 
INNER JOIN `T_COMMANDES` ON `T_RELATIF_SESS`.`REL_COM_ID` = `T_COMMANDES`.`COM_ID`