CODEWITHSUNDEEP
sqlsyntax-error
Zabon
Zabon

Reputation: 245

Where is my syntax error in this SQL request?

I'm getting a syntax error near WHERE tbkay.keyword = 'ipsum' in the following request (using phpmyadmin): 

SELECT tbart.articles_id FROM articles AS tbart
    LEFT JOIN keywords AS tbkey 
    LEFT JOIN articles_keyword AS tbjoin 
        ON tbart.articles_id = tbjoin.articles_id 
        AND tbkey.id = tbjoin.keywords_id
    WHERE tbkey.keyword = 'ipsum'

My tables

I don't get it.

Upvotes: 0

Views: 62

Answers (2)

forpas
forpas

Reputation: 164064

You are not using an ON clause after each join:

SELECT tbart.articles_id 
FROM articles AS tbart
INNER JOIN articles_keyword AS tbjoin ON tbart.articles_id = tbjoin.articles_id 
INNER JOIN keywords AS tbkey ON tbkey.id = tbjoin.keywords_id
WHERE tbkey.keyword = 'ipsum'

I also changed the joins to INNER joins because the WHERE clause returns only matching rows.

If you want to keep the LEFT join and return all the rows of articles you must move this condition to the ON clause:

SELECT tbart.articles_id 
FROM articles AS tbart
LEFT JOIN articles_keyword AS tbjoin ON tbart.articles_id = tbjoin.articles_id 
LEFT JOIN keywords AS tbkey ON tbkey.id = tbjoin.keywords_id AND tbkey.keyword = 'ipsum'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Each JOIN should have a corresponding ON clause:

SELECT tbart.articles_id
FROM articles tbart LEFT JOIN
     articles_keyword tbjoin 
     ON tbart.articles_id = tbjoin.articles_id LEFT JOIN
     keywords tbkey 
     ON tbkey.id = tbjoin.keywords_id
WHERE tbkey.keyword = 'ipsum' ;

Your version has two JOINs and one ON, hence the error.

Upvotes: 1

Related Questions