A for loop in MYSQL WHERE clause?

Question

Is it possible to have a forloop in WHERE clause, something like

SELECT
  name,
  surname,
  id
FROM person
WHERE (while(x < 5)) = id

then the result will have id's from 1 to 4. The 5 can have a list of values or an array.

Is this even possible?

ps. this example can be done by using WHERE 5 > id

I was just wondering if this can be done with a loop, I will be using this query snippet in jasper report

It doesn't make sense. The `WHERE` clause is for establishing predicates about the columns in each row of the result set. You do that with comparisons, `EXISTS`, `IN` and other tools. I can't think of any situation where a loop makes sense. Perhaps if you could propose an example that *isn't* trivially re-writable as a normal predicate, we might be able to help. If you can't think of such an example, it kind of demonstrates the point that it's unnecessary. — Damien_The_Unbeliever, Jan 22 '13 at 07:33
Refer http://stackoverflow.com/questions/6226447/passing-sql-in-parameter-list-in-jasperreport — Rajesh Omanakuttan, Jan 22 '13 at 07:53

score 3 · Accepted Answer · answered Jan 22 '13 at 07:20

3

Do you mean something like:

SELECT name, surname, id 
FROM person
WHERE id in(1, 2, 3, 4);

If this list of numbers comes from an array or a list you can compose this list from your front end application, or create a temp table from those values and JOIN the table with it.

answered Jan 22 '13 at 07:20

Mahmoud Gamal

78,257
17
139
164

Yes, but the values inside IN clause came from a list or an array.? – noob Jan 22 '13 at 07:22
@user1816978 You can compose this `IN` clause from your application using these values that come from the array or a list, or use a temp table and `JOIN` the table with it. – Mahmoud Gamal Jan 22 '13 at 07:25

score 2 · Answer 2 · answered Jan 22 '13 at 07:33

You cannot do it using WHERE clause. You need a construct that returns the value to use it in a condition.

The WHILE statement returns nothing, it is just a statements list.
The condition 5 > id returns value that can be used in WHERE clause.

Note, in stored procedure you could do something like this -

  DECLARE i INT DEFAULT 0;
  WHILE i < 5 DO
    SELECT * FROM table WHERE id = i;
    SET i = i + 1;
  END WHILE;

...but it is not efficient.

score 2 · Answer 3 · answered Jan 22 '13 at 07:40

Based on your comment:

The 5 can have a list of values or an array

You might be after something like this:

select id, name, surname
from person
where find_in_set(id, '1,2,3,4') > 0;

SQLFiddle example: http://sqlfiddle.com/#!2/7186d/3

score 1 · Answer 4 · answered Jan 22 '13 at 07:34

1

You can use BETWEEN command if you know from where to where you want to get data. As like following command ...

   SELECT name, surname, id FROM person WHERE id BETWEEN 1 AND 5 ;

answered Jan 22 '13 at 07:34

Md. Maruf Hossain

912
9
11

score 0 · Answer 5 · answered Jan 22 '13 at 07:28

You can use a simple query with where condition like this and it is what exactly you are asking for i mean alternative of loop. This does not require loops when the query is executed it executes like loop

SELECT
    p.name, 
    p.surname, 
    p.id
FROM person p
WHERE p.id < 5

this will check the records with id starting from 1 to 4

A for loop in MYSQL WHERE clause?

5 Answers5