Put two equal conditions into one WHERE clause - SQL

user_6907455 0
Viewed 125

I am doing the SQL exercise and got a question about WHERE clause.

 - Write an SQL query to find all numbers that appear at least three times consecutively. 

*Logs table:*

id   Num
 1    1
 2    1
 3    1
 4    2
 5    1
 6    2
 7    2

My sql query code is:

SELECT DISTINCT a.Num AS ConsecutiveNums FROM Logs AS a
INNER JOIN Logs AS b ON  a.Id + 1 = b.Id
INNER JOIN Logs AS c ON a.Id + 2 = c.Id
WHERE a.Num = b.Num = c.Num;

When I write condition as WHERE a.Num = b.Num = c.Num, the result shows {"headers": ["ConsecutiveNums"], "values": [[1], [2]]}, which is not correct. But when I separate the condition to WHERE a.Num = b.Num AND b.Num = c.Num, the result is {"headers": ["ConsecutiveNums"], "values": [[1]]}, which is correct.

Could anyone please tell the principle of the WHERE clause regarding this situation?

mysql sql
3 Answers

The correct way to do it is with:

WHERE a.Num = b.Num AND b.Num = c.Num

When you use:

WHERE a.Num = b.Num = c.Num

the result you get is coincidental.

It has to do with the operator = and MySql's feature to evaluate boolean expressions like x = y as 1 for true and 0 for false.

So, this boolean expression:

a.Num = b.Num = c.Num

is evaluated as:

(a.Num = b.Num) = c.Num

and since the result of (a.Num = b.Num) can be either 0 or 1, the whole expression is equivalent to:

0 = c.Num

or:

1 = c.Num

But there is no row with Num = 0 in the table, so the only case that the boolean expression will evaluate to 1 (true) is if Num = 1 and this is the result you get (coincidentally).

user_104988280 
SELECT DISTINCT NUM
FROM Logs as main
WHERE
NUM = (SELECT NUM FROM Logs WHERE ID = main.ID +1)
AND NUM = (SELECT NUM FROM Logs WHERE ID = main.ID +2)

Since this looks like a piece of homework, I hope you understand how each part of the query work, and why it works.

user_163681180 
select distinct l1.Num as ConsecutiveNums
from 
Logs l1, 
Logs l2, 
Logs l3
where l1.Id = l2.Id-1
and l2.Id = l3.Id-1
and l1.Num=l2.Num
and l2.Num=l3.Num

Hope this helps you

user_164114990
Related