Re: Duplicate records returned

In article <4680D3E9(dot)7020706(at)archonet(dot)com>,
Richard Huxton <dev(at)archonet(dot)com> writes:

>> $the_sql = " SELECT projectname, username, sum(hours)";
>> $the_sql .= " FROM timerecs";
>> $the_sql .= " WHERE projectname = projects.projectname ";
>> $the_sql .= " AND projectname = restrictions.projectname";
>> $the_sql .= " AND projects.parent = 'Projects'";
>> $the_sql .= " AND projects.pct < 100";
>> $the_sql .= " AND restrictions.hidden = 5";
>> $the_sql .= " AND projectname = 'Testing'"; # just for tsting
>> $the_sql .= " AND username = 'long'"; # just for testing
>> $the_sql .= " AND projectname = projects.projectname ";
>> $the_sql .= " GROUP BY projectname, username";
>> $the_sql .= " ORDER BY projectname, username";
>> $the_sql .= " ;";

> You might want to read up on "HERE documents" for multi-line blocks of text.

In case the above code is Perl, I think

my $sql = q{
SELECT ...
FROM ...
WHERE ...
GROUP ...
};

looks nicer than a here-document.

> 1. You've also not put all your tables into the FROM clause:
> FROM timerecs, projects, restrictions
> This *should* be generating a warning of some kind
> 2. You're not qualifying which column comes from which table, which
> makes it harder to see what's happening. Try:
> FROM timerecs t, projects p, restrictions r
> WHERE t.projectname = p.projectname
> AND ...
> That's called table aliasing, where you give a short name to tables.

This still mixes JOIN conditions with other result restrictions.

SELECT ...
FROM timerecs t
JOIN projects p ON p.projectname = t.projectname
...

makes it more explicit.