CODEWITHSUNDEEP
phpmysql
Richard
Richard

Reputation: 289

What is wron with this MySQL join query

I have a breaks and clock_in_out table and I want to get the all values from both and join them on their user_id column. But I am getting incorrect synatx to use near JOIN ON, not a clue why.. Here is my query

SELECT * 
FROM `breaks` 
WHERE `user_id` = 1 
AND `in_date` LIKE '%2015-03-03%' 
AND `out_date` LIKE '%2015-03-03%' 
JOIN `clock_in_out` ON `breaks`.`user_id` = `clock_in_out`.`user_id` 
WHERE `loggedin_date` 
LIKE '%2015-03-03%' 
AND `loggedout_date` LIKE '%2015-03-03%'

Upvotes: 0

Views: 42

Answers (4)

varad mayee
varad mayee

Reputation: 619

Correct syntax for the this is as follows

    SELECT * 
FROM `breaks` 
JOIN `clock_in_out` ON `breaks`.`user_id` = `clock_in_out`.`user_id` 
WHERE `user_id` = 1 
AND `in_date` LIKE '%2015-03-03%' 
AND `out_date` LIKE '%2015-03-03%' 
AND `loggedin_date` LIKE '%2015-03-03%' 
AND `loggedout_date` LIKE '%2015-03-03%

Upvotes: 0

Jens
Jens

Reputation: 69440

Your syntax is wrong. The correct syntax for join is Select ... from .. join ... on ... where

SELECT * 
FROM `breaks` 
JOIN `clock_in_out` ON `breaks`.`user_id` = `clock_in_out`.`user_id` 
WHERE `loggedin_date` 
LIKE '%2015-03-03%' 
AND `loggedout_date` LIKE '%2015-03-03%'
AND `user_id` = 1 
AND `in_date` LIKE '%2015-03-03%' 
AND `out_date` LIKE '%2015-03-03%'

Upvotes: 1

Barmar
Barmar

Reputation: 780909

You can't mix WHERE and JOIN clauses like that. The order is: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, LIMIT. So it should be:

SELECT * 
FROM `breaks` 
JOIN `clock_in_out` ON `breaks`.`user_id` = `clock_in_out`.`user_id` 
WHERE `user_id` = 1 
AND `in_date` LIKE '%2015-03-03%' 
AND `out_date` LIKE '%2015-03-03%' 
AND `loggedin_date` LIKE '%2015-03-03%' 
AND `loggedout_date` LIKE '%2015-03-03%'

See the MySQL documentation for the complete syntax.

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18737

WHERE clause should be after JOIN:

SELECT * 
FROM `breaks` JOIN 
     `clock_in_out` ON `breaks`.`user_id` = `clock_in_out`.`user_id`
WHERE `user_id` = 1 
  AND `in_date` LIKE '%2015-03-03%' 
  AND `out_date` LIKE '%2015-03-03%' 
  AND `loggedin_date` LIKE '%2015-03-03%' 
  AND `loggedout_date` LIKE '%2015-03-03%'

Upvotes: 2

Related Questions