LibreOffice Base MySQL query gives error 1064 in phpMyAdmin

[Amedee Van Gasse]

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. ;-)