3

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.

2 Answers 2

2

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 !!!

4
  • Thank you, it works but it is very slow, maybe because it is sub query?
    – Deano
    May 17, 2013 at 20:50
  • Is StartTime indexed ??? May 17, 2013 at 20:51
  • okay add index and try
    – Deano
    May 17, 2013 at 21:01
  • Did the index on StartTime help ? May 17, 2013 at 21:37
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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