This is continuous from my previous question. I'm using RMySQL package in R to send a SQL suery to the MySQL database. The error says something is wrong with the syntax, but I cannot find the error part. Could anybody help me fix this error?
The data table looks like this:
organisation Tour_ID A B C D
Ikea a 2018-04-01 2018-05-07 2018-05-09 2018-05-01
Ikea a 2018-06-01 2018-05-03 2018-05-29 NA
Ikea a 2018-04-02 2018-05-01 2018-07-08 2018-05-26
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b NA 2018-05-05 2018-08-02 2018-06-01
Ikea c 2018-06-01 2018-05-07 2018-05-09 2018-05-01
Ikea c 2018-06-01 2018-05-03 NA NA
Ikea c 2018-08-02 2018-05-09 2018-07-08 2018-05-26
And the syntax I wrote is:
cond <- 'SELECT t.*
FROM myTable t JOIN
(SELECT organisation, Tour_ID
FROM myTable
WHERE organisation LIKE "Ikea" AND
GROUP BY organisation, Tour_ID
HAVING LEAST(COALESCE(MIN(A), "2119-01-01"),
COALESCE(MIN(B), "2119-01-01"),
COALESCE(MIN(C), "2119-01-01"),
COALESCE(MIN(D), "2119-01-01")) >= "2018-05-01 00:00" AND
LEAST(COALESCE(MIN(A), "2119-01-01"),
COALESCE(MIN(B), "2119-01-01"),
COALESCE(MIN(C), "2119-01-01"),
COALESCE(MIN(D), "2119-01-01")) < "2018-05-31 00:00"
) tt ON
tt.Tour_ID = t.Tour_ID AND
tt.organisation = t.organisation'
dbGetQuery(conn = connection, statement = cond)
And the error message I get is:
Error in .local(conn, statement, ...) : could not run statement:
You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax
to use near 'GROUP BY organisation, Tour_ID HAVING LEAST(COALESCE(MIN(A' at line 1
But I see no issue at GROUP BY part! What's wrong with my syntax?
