Query WHERE clause and JOIN

Question

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.

Answer 1

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

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