LibreOffice Base MySQL query gives error 1064 in phpMyAdmin

[Amedee Van Gasse]

On 05/20/11 23:06, Amedee Van Gasse wrote:
> On 05/20/11 21:47, Jordon Bedwell wrote:
>> On 5/20/2011 2:27 PM, Amedee Van Gasse wrote:
>>> SELECT YEAR( 'logfiledate' ), MONTH( 'logfiledate' ),
>>> 'Sessions'.'UserId', 'Users'.'Name', 'Users'.'FirstName', 'Users'.'Cat',
>>> SUM( 'Sessions'.'TotalTime' ) FROM 'loganalyser'.'Sessions' AS
>>> 'Sessions', 'loganalyser'.'Users' AS 'Users', 'loganalyser'.'Logfiles'
>>> AS 'Logfiles' WHERE 'Sessions'.'UserId' = 'Users'.'Trigram' AND
>>> 'Sessions'.'LogFile' = 'Logfiles'.'logfile' AND YEAR( 'logfiledate' ) =
>>> '2011' AND MONTH( 'logfiledate' ) = '12' GROUP BY 'Sessions'.'UserId'
>>> ORDER BY SUM( 'Sessions'.'TotalTime' ) DESC
>>
>> Your critical error is 'Sessions'.'UserId' it should be sessions.userid
>> without any wrap, without wrapping table and row and the concatenating,
>> it should simply be table.row without anything else.
>
> Thanks - it worked without the quotes.
> Now I have 0 results while LibreOffice Base shows me the records that I
> expected - AND it uses quotes. Stupid LibreOffice with it's generated
> query. I thought I could take a shortcut, unfortunately not. Meh.
>
>> I'm a bit confused about what you're doing anyways, this query seems
>> like it would be much better suited as a JOIN instead of an unoptimised
>> long query :P.
>
> You are absolutely right, but the query was generated by LibreOffice
> Base so if it bothers you, you know where to file the bug report. :)
> In my defense: it's been 3 years since I last wrote a query with a JOIN,
> and I wasn't good at it back then anyway.
> And one can always refactor later. ;-)
>

Same query, rewritten from scratch:

SELECT YEAR(logfiledate) AS Year, MONTH(logfiledate) AS Month, UserId, 
Name, FirstName, Cat, SUM(TotalTime) AS TotalTime
FROM Sessions
LEFT JOIN Logfiles on Sessions.LogFile = Logfiles.logfile
LEFT JOIN Users on UserId = Trigram
WHERE YEAR(logfiledate) = 2010
AND MONTH(Logfiles.logfiledate) = 12
GROUP BY UserId
ORDER BY TotalTime DESC, UserId ASC
LIMIT 0, 10

Much cleaner, methinks.
Thanks to all who contributed, or for just being my wall.
Case closed.