MySQL error for join query

Question

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

dpriver.com/pp/sqlformat.htm also tells you if there are any syntax errors — Tauseef
– Tauseef, Commented Oct 7, 2013 at 12:58

Vivek Singh · Accepted Answer · 2013-10-07 13:20:51Z

2

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.

edited Oct 7, 2013 at 13:20

answered Oct 7, 2013 at 13:01

Vivek Singh

2751 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deepak Rai · Accepted Answer · 2013-10-07 12:48:17Z

0

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

answered Oct 7, 2013 at 12:48

Deepak Rai

2,2113 gold badges24 silver badges39 bronze badges

1 Comment

Tauseef Over a year ago

request_results is the table itself. Should remove it actually.

Kevin Walter · Accepted Answer · 2013-10-07 12:48:41Z

0

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;

answered Oct 7, 2013 at 12:48

Kevin Walter

7,26610 gold badges31 silver badges33 bronze badges

Comments

Tauseef · Accepted Answer · 2013-10-07 12:53:32Z

0

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

answered Oct 7, 2013 at 12:53

Tauseef

2,05219 silver badges17 bronze badges

Comments

Zagor23 · Accepted Answer · 2013-10-07 12:59:25Z

0

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)

edited Oct 7, 2013 at 12:59

answered Oct 7, 2013 at 12:49

Zagor23

1,96312 silver badges14 bronze badges

Comments

A. Zalonis · Accepted Answer · 2013-10-07 13:21:19Z

0

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;

answered Oct 7, 2013 at 13:21

A. Zalonis

1,6096 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL error for join query

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related