Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having an issue using a WHERE clause and JOIN.

SELECT * FROM `CallDetailRecord` 
  WHERE `StartTime` >=1357102799000 
  AND `StartTime` <=1357880399000 
  JOIN `CallEvent` ON `EventID` = `CallEventID`

Error Code: 1064
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 'JOIN CallEvent ON EventID = CallEventID
LIMIT 0, 1000' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.004 sec
---------------------------------------------------

I'm just trying to limit data set by time "starttime', but I get query error and it refers to join.

Any ideas? I will be happy to provide more info if required.

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

Here is your original query

SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = `CallEventID`
LIMIT 1000

In theory, you can do the following

  • Subquery that has WHERE on the StartTime
  • Add ORDER BY StartTime
  • Do the LIMIT inside the subsquery
  • Specify a LEFT JOIN

Here is such a query

SELECT * FROM
(
    SELECT * FROM `CallDetailRecord`
    WHERE `StartTime` >=1357102799000
    AND `StartTime` <=1357880399000
    ORDER BY StartTime
    LIMIT 0,1000
) A LEFT JOIN `CallEvent` B ON `EventID` = `CallEventID`;

If the query is slow, then add an index on StartTime

ALTER TABLE CallDetailRecord ADD INDEX (StartTime);

Give it a Try !!!

share|improve this answer
    
Thank you, it works but it is very slow, maybe because it is sub query? –  Deano May 17 '13 at 20:50
    
Is StartTime indexed ??? –  RolandoMySQLDBA May 17 '13 at 20:51
    
okay add index and try –  Deano May 17 '13 at 21:01
    
Did the index on StartTime help ? –  RolandoMySQLDBA May 17 '13 at 21:37
add comment

Your WHERE clause must be after your join, perhaps?

SELECT * 
FROM CallDetailRecord
    JOIN CallEvent ON EventID = CallEventID
WHERE StartTime >= 1357102799000 AND StartTime <=1357880399000 

Note: You may want to prefix the fields with table names or aliases...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.