CODEWITHSUNDEEP
mysqljoin
NeoTechni
NeoTechni

Reputation: 179

I can't get a LEFT JOIN to work

SELECT *, 0 AS distance
FROM restaurants
WHERE   OPEN = '1'
    AND STATUS = '1'
    AND is_delivery = '1'
    AND NAME LIKE '%chuck%'
ORDER BY distance LIMIT 0, 10

Here's the base SQL query I have to work with. I want to add the restaurant's hours for today. All the bloody documentation says it should be as easy as adding 

 LEFT JOIN hours ON (restaurants.id = hours.restaurant_id AND hours.day_of_week = 'Tuesday')

To combine them, I tried:

SELECT *, 0 AS distance
FROM restaurants
WHERE   OPEN = '1'
    AND STATUS = '1'
    AND is_delivery = '1'
    AND NAME LIKE '%chuck%'
ORDER BY distance LIMIT 0, 10
LEFT JOIN hours ON (restaurants.id = hours.restaurant_id AND hours.day_of_week = 'Tuesday')

But it just gives me an unhelpful error on the LEFT JOIN.

"#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 'LEFT JOIN hours ON (restaurants.id = hours.restaurant_id AND hours.day_of_week =' at line 1"

Upvotes: 0

Views: 738

Answers (2)

JNevill
JNevill

Reputation: 50034

I'm guessing that you stuck your LEFT JOIN at the end of your SQL statement and it freaked out on you (or something like that), but it's hard to tell since you didn't include the SQL statement that lead to the error you show there.

All joins in a query take place in the FROM clause of the SQL statement. This is the part of the SQL statement that sets which tables/views you are selecting from and their relationship to each other.

At any rate, the following should work:

SELECT *,
    0 AS distance
FROM restaurants
LEFT JOIN hours ON
        restaurants.id = hours.restaurant_id
        AND hours.day_of_week = 'Tuesday'

WHERE
    OPEN = '1'
    AND STATUS = '1'
    AND is_delivery = '1'
    AND NAME LIKE '%chuck%'
ORDER BY distance 
LIMIT 0,10

It's a bit odd that you are ordering by distance here which is always 0, but perhaps there is a reason for that that is also not stated.

Upvotes: 3

PookMook
PookMook

Reputation: 216

your SQL request should look like 

SELECT *, 0 AS distance FROM restaurants 
LEFT JOIN hours ON (restaurants.id = hours.restaurant_id AND hours.day_of_week = 'Tuesday')
WHERE open = '1' AND status = '1' AND is_delivery = '1' AND name LIKE '%chuck%' ORDER BY distance LIMIT 0, 10

JOINs come after the FROM field statement

Upvotes: 0

Related Questions