CODEWITHSUNDEEP
mysqlsql
bsky
bsky

Reputation: 20222

Syntax error on LEFT OUTER JOIN

I have the following MySQL query:

SELECT w1.Id
FROM Wills w1 LEFT OUTER JOIN Wills w2
WHERE w1.Id = w2.Id + 1
AND w1.Tmp > w2.Tmp

With the query, I want to compare rows with adjacent Ids. I want to select those that have a greater Tmp field than their predecessor.

However, I get the following error:

Runtime Error Message:
Line 3: SyntaxError: near 'WHERE w1.Id = w2.Id + 1
AND w1.Tmp > w2.Tmp'

I don't have much experience with SQL and MySQL. Did I do the join incorrectly?

Upvotes: 0

Views: 367

Answers (2)

Jens
Jens

Reputation: 69440

You miss the ON clause:

SELECT w1.Id
FROM Wills w1 LEFT OUTER JOIN Wills w2 ON w1.Id = w2.Id + 1
and  w1.Tmp > w2.Tmp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You need to put the conditions in the ON clause rather than a WHERE clause:

SELECT w1.Id
FROM Wills w1 LEFT OUTER JOIN 
     Wills w2
     ON w1.Id = w2.Id + 1 AND w1.Tmp > w2.Tmp;

If either condition is in the WHERE, then the LEFT JOIN will be turned into an INNER JOIN.

Upvotes: 1

Related Questions