June_Stephanie - 4 years ago 90

MySQL Question

I write a SQL query to find all numbers that appear at least three times consecutively:

| ** Id** |

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 1 |

| 6 | 2 |

| 7 | 2 |

For example, given the above

My original query returns 1 and 2:

`SELECT l1.Num`

FROM Logs l1, Logs l2, Logs l3

WHERE l1.Id + 2 = l2.Id + 1 = l3.Id

AND l1.Num = l2.Num = l3.Num;

+------------------------------------------------------------------------

Then I change my query to this (basically I change the a+2 = b+1 = c to a + 1 = b and b+1 = c), it returns the correct answer:

`SELECT l1.Num`

FROM Logs l1, Logs l2, Logs l3

WHERE l1.Id + 1 = l2.Id AND l2.Id + 1 = l3.Id

AND l1.Num = l2.Num AND l1.Num= l3.Num;

+------------------------------------------------------------------------

It drives me crazy, I cannot figure our why. Could anyone kindly explain this to me? Thanks in advance!!!!

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

Well, let's have a look at this query:
```
WHERE l1.Id + 2 = l2.Id + 1 = l3.Id
```

In MySQL as in most programming languages, the `=`

is just a binary operator, which means it takes a left and a right side and returns true if they are the same and false if otherwise.

In your query, you're first comparing `l1.Id + 1`

with `l2.Id`

resulting in a truth value. And then, you compare the truth value with `l3.Id`

. (The truth value gets automatically casted to `0`

/`1`

). This leads to a different result.

tl;dr: `=`

in MySQL is not like `=`

often used in mathematical notation.

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**