Search code examples
mysqljoincomparisoncase

MySQL Case when joining two tables

I have a question concerning mySQL :

I have two tables.

Table 1

Table 1

In the table 2, I have the same 5 idprojet, but with various datedebut and various datefin.

Table 2

I need to fin a way to know if the smaller "datedebut" for each project in table2 is smaller than the datedebut from table1, and if the biggest "datefin" for each project in table2 is bigger than the datefin from table1.

I wrote this request 

SELECT table1.idprojet,
case when table1.datedebut < table2.tab2min then 'False'
else 'True' end as dateDebutComparison,
case when table2.dateFin > table1.dateFin then 'True'
else 'False' end as dateFinComparison
FROM table1, table2
JOIN (select table2.idProjet, MIN(dateDEBUT) tab2min, MAX(dateFIN) tab2max FROM table2 GROUP BY idProjet) table2
ON table2.idProjet = table1.idProjet

But it gives me the following mistake :

Error Code: 1052. Column 'table2.idProjet' in on clause is ambiguous

The outpout would be something like

enter image description here

Solution

  • The table2 does not need to appear in your main query.
    The following query should be right. I changed the subquery alias to avoid confusion with the table2 table name.

    SELECT 
    table1.idprojet,
    CASE WHEN table1.datedebut < minMaxTable2.tab2min THEN 'False' ELSE 'True' END AS dateDebutComparison,
    CASE WHEN minMaxTable2.tab2max > table1.dateFin THEN 'True' ELSE 'False' END AS dateFinComparison
FROM table1
     JOIN (SELECT table2.idProjet, MIN(table2.dateDEBUT) tab2min, MAX(table2.dateFIN) tab2max 
           FROM table2
           GROUP BY table2.idProjet
     ) minMaxTable2
ON minMaxTable2.idProjet = table1.idProjet