In article <4680D3E9.7020706@archonet.com>,
Richard Huxton <dev@archonet.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.
