How to add if elseif condition in where clause on mysql selection?

Question

I am working on selecting data according to 3 basic condition which is not the issue,the issue is that if 1st condition satisfied then it should not consider the rest two conditions just like if else but if the 1st condition is false then it goes to 2nd only not to 3rd one just like if elseif and if first two conditions not satisfied then it will consider the 3rd only.

But in mysql when i use OR & AND then it is considering all 3 conditions at a time.

Like this:-

SELECT * FROM `table` WHERE 'main-condition' and ('1st condition' or '2nd condition' or '3rd condition');

After this i also tried with IF in where clause but it also give the same result like OR.

Code:-

SELECT * FROM `table` WHERE 'main-condition' and (IF('1st condition',true,IF('2nd condition',true,IF('3rd condition',true,false))));

Actual Query:-

SELECT 'filter','car-name' FROM `car` WHERE (((`id` = ? and `type` = 'car')and(IF((find_in_set('new car',`filter`) > 0),true,IF((find_in_set('new',`filter`) > 0, true,if((`filter`=""),true,false))))));

It give output

But i need output like this

If any one knows how to handle this type of query then please answer me.

Thanks

Possible duplicate of ['IF' in 'SELECT' statement - choose output value based on column values](http://stackoverflow.com/questions/5951157/if-in-select-statement-choose-output-value-based-on-column-values) — Masivuye Cokile, May 04 '17 at 11:29
[Usig if statement in mysql select](http://stackoverflow.com/questions/8444570/using-an-if-statement-in-a-mysql-select-query) — Masivuye Cokile, May 04 '17 at 11:30
And pls post sample data and expected output. But honestly, I do not get your question. Why does it matter if a record satisfies both the 1st and the second criteria? Or do you only want records that satisfy only one of the 3 criteria? — Shadow, May 04 '17 at 11:34
Yes,I just want records that satisfy only one of the 3 criteria with order like if elseif else . — Bibhudatta Sahoo, May 04 '17 at 11:38
I am already tried with CASE WHEN THEN also but still i am not get the desires o/p. — Bibhudatta Sahoo, May 04 '17 at 12:01

score 1 · Answer 1 · answered May 08 '17 at 13:05

1

To do so you need to iterate to all records present in your Database with different conditions. So better is to fetch all records with all possibilities and then filter using PHP script.

answered May 08 '17 at 13:05

Shams Reza

1,087
8
10

score 1 · Accepted Answer · answered May 08 '17 at 14:19

I have research on this for 2-3 days then only i got that we can't get the desired output as i mentioned above because mysql queries execute with respect to table rows separately not with the whole table.

But i solved this by using mysql Group_concat()

Query:-

SELECT Group_concat( CASE WHEN (find_in_set('new car',`filter`) > 0) THEN car-name END separator ',') exactCars,Group_concat( CASE WHEN (find_in_set('new',`filter`) > 0) THEN car-name END separator ',') possibleCars,Group_concat( CASE WHEN (`filter`="") THEN car-name END separator ',') defaultCars, FROM `car` WHERE (`id` = ? and `type` = 'car');

This provide output like this

Now just using php Explode() with the result then get the desired data like

$exactCars=explode(',',$reult->exactCars);

It give output as

How to add if elseif condition in where clause on mysql selection?

2 Answers2