CODEWITHSUNDEEP
mysqlinner-join
van
van

Reputation: 9449

MySQL error for join query

Not sure what the error is with the query below?

select  r.request_id, rr.request_result_id,r.date_submitted
from request_results rr 
  inner join requests r on rr.request_id = r.request_id 
  where ((rr.file_size IS NULL) or length(rr.results) = 0) 
     and r.date_submitted >= CURDATE() 
order by r.request_id, rr.request_result_id request_results;

ERROR 1064 (42000): 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 'request_results' at line 1

Upvotes: 2

Views: 90

Answers (6)

A. Zalonis
A. Zalonis

Reputation: 1609

In order by you missing a comma Try this code

select  r.request_id, rr.request_result_id,r.date_submitted
from request_results rr 
  inner join requests r on rr.request_id = r.request_id 
  where ((rr.file_size IS NULL) or length(rr.results) = 0) 
     and r.date_submitted >= CURDATE() 
order by r.request_id, rr.request_result_id, r.date_submitted;

Upvotes: 0

Vivek Singh
Vivek Singh

Reputation: 275

replace

ORDER BY r.request_id, rr.request_result_id, X.request_results;

in the end by

ORDER BY r.request_id, rr.request_result_id, r.date_submitted;

because the columns specified in ORDER BY clause should be one of the columns selected in the SELECT column list.

Upvotes: 2

Zagor23
Zagor23

Reputation: 1963

Remove request_results from the end, it's a name of a table.

select 
    r.request_id, rr.request_result_id, r.date_submitted
from
    request_results rr
        inner join
    requests r ON rr.request_id = r.request_id
where
    ((rr.file_size IS NULL)
        or length(rr.results) = 0)
        and r.date_submitted >= CURDATE()
order by r.request_id , rr.request_result_id;

And try to write cleaner, well formatted code (or use auto-format option like the one you have in MySQL workbench)

Upvotes: 0

Tauseef
Tauseef

Reputation: 2052

You have the table name added at the end of query for no reason

    SELECT r.request_id,
       rr.request_result_id,
       r.date_submitted
FROM   request_results rr
       INNER JOIN requests r
               ON rr.request_id = r.request_id
WHERE  ( ( rr.file_size IS NULL )
          OR Length(rr.results) = 0 )
       AND r.date_submitted >= Curdate()
ORDER  BY r.request_id,
          rr.request_result_id

For queries like these, use the SQL formatters if you don't want to mess yourself. the one I use is http://www.dpriver.com/pp/sqlformat.htm

Upvotes: 0

Kevin Walter
Kevin Walter

Reputation: 7166

You missed one comma at the end. Try this one but replace the X with the correct table alias

SELECT r.request_id, rr.request_result_id, r.date_submitted
    FROM request_results rr INNER JOIN requests r ON rr.request_id = r.request_id
    WHERE ((rr.file_size IS NULL) or length(rr.results) = 0) and r.date_submitted >= CURDATE()
    ORDER BY r.request_id, rr.request_result_id, X.request_results;

Upvotes: 0

Deepak Rai
Deepak Rai

Reputation: 2203

I think problem is here

order by r.request_id, rr.request_result_id request_results

what does 'request_results' refers to here ?

change it to 

order by r.request_id, rr.request_result_id, r.request_results

Upvotes: 0

Related Questions