Topic: Group by Clause and Left Joins

[SchweppesAle]

Hi, I just want to make sure that I have a solid understanding of what's going on here.  I have the following query.

Code: [Select]

"SELECT products.productid
                        , products.measurement_id
                        , measurements.id
                        , measurements.name
                     FROM measurements
                        LEFT JOIN products ON
                            (measurements.id = products.measurement_id)
                                GROUP BY measurements.id"

It works fine, I receive a list of measurements and some products which may/may not be have a products.measurement_id value which relates the two tables.  However, I noticed that without the GROUP BY clause, if I have multiple entries within products which relate to the table, I will receive duplicate entries. 

This may be due in part to a misunderstanding of how LEFT Join actually works.  From my understanding, you specify a control table "FROM measurements" which then searches a corresponding table "LEFT JOIN products ON"  - (then the condition).  If a match is found, it's included with a new "joined" table, if not then the attributes from the Products table for that entry remains NULL. 

This is in contrast to an INNER JOIN which will simply neglect that entry on BOTH tables if a match is not found.  I was hoping someone could clarify how this actual works. 

Also, I was told once on this forum that you should always run mysql's COUNT() function on a specific attribute on the controlling(?) table's ID in order to be in compliance with some standards(but only when the GROUP BY clause is used(?)). 

What's the reasoning behind this? 

Thanks again, I can' tell you how frequently I turn to this forum as a resource; and it's paid off.

You guys rock.

[fenway]

This "duplicate" issue has nothing to do with LEFT JOIN vs INNER JOIN.  You'll get one "record" for each matching pair, so if it's not a 1-to-1, you'll have MxN results returned.