CODEWITHSUNDEEP
sqlleft-joinaggregate-functions
Entretoize
Entretoize

Reputation: 2251

Adding LEFT JOIN add results or modify them

My tables are designed like that: cours>modules>sections>parts I mean there's courses, inside courses there's modules, inside modules there's sections...

This is my actual query:

SELECT PG.idpage,PG.type
                FROM pages PG
                JOIN parts P            ON P.idpart=PG.idpart
                JOIN sections S         ON S.idsection=P.idsection
                JOIN modules M          ON M.idmodule=S.idmodule
                JOIN coursuser CU       ON CU.idcours=M.idcours     
                WHERE PG.idpart='15' AND CU.iduser='1'
                ORDER BY idpage;

What I'm trying:

SELECT PG.idpage,PG.type,R.time
                FROM pages PG
                JOIN parts P            ON P.idpart=PG.idpart
                JOIN sections S         ON S.idsection=P.idsection
                JOIN modules M          ON M.idmodule=S.idmodule
                JOIN coursuser CU       ON CU.idcours=M.idcours     
                LEFT JOIN reads R   ON R.idpage=PG.idpage   
                WHERE PG.idpart='15' AND CU.iduser='1'
                ORDER BY idpage;

I expected to have the same results with just the columns of reads added, but this is not the case, why ?

Upvotes: 0

Views: 47

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

It's a matter of which first row you'd want to use from reads table. Normally this is the case for aggregate functions, or some nested subquery.

For example if you wanted to take maximum time from reads table you would need to add a GROUP BY clause and apply an aggregate function retrieving maximum value MAX(R.time) from reads table.

SELECT PG.idpage, PG.type, MAX(R.time) as time
                FROM pages PG
                JOIN parts P            ON P.idpart=PG.idpart
                JOIN sections S         ON S.idsection=P.idsection
                JOIN modules M          ON M.idmodule=S.idmodule
                JOIN coursuser CU       ON CU.idcours=M.idcours     
                LEFT JOIN reads R   ON R.idpage=PG.idpage   
                WHERE PG.idpart='15' AND CU.iduser='1'
                GROUP BY PG.idpage, PG.type
                ORDER BY idpage;

Upvotes: 1

Related Questions