Mysql Select within IF condition

Question

looking to perform a query that on a particular conditions gets the data from another table.

it like

select field1, field2.... IF(fieldX=3,select value from sub_table where cat_id = 3 order by id desc limit 0,1, NULL) from abc ;

The query within the if is valid. I am used to with implementing if conditions without any issue but those were all for some static values or a field. But, this is the first time I am trying to get a select's result in if and unable to do it.

The case is because for some particular value of 'fieldX' I need to get a record from another table.

Regards Junaid

mikeq · Accepted Answer · 2011-06-21 15:51:54Z

2

wrap you inner select in ( )

IF(fieldX=3, (select value from sub_table where cat_id = 3 order by id desc limit 0,1), NULL)

answered Jun 21, 2011 at 15:51

mikeq

8135 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mikeq Over a year ago

oh and you dont need limit 0,1 just limit 1 will do if you want just one record

Tommy Adey Over a year ago

This imo is the perfect answer to this question.

knittl · Accepted Answer · 2011-06-21 16:09:18Z

0

why not use a left join and use fieldX=3 as a join condition? if fieldX is different from 3, sql fills the field with NULL

select a.field1, a.field2, sub.value
from abc a
left join
(
  select value from sub_table
  where cat_id = 3
  limit 0,1
) sub
on a.fieldX = 3

or, if you do want to get all rows for the corresponding values (i see you have cat_id = 3 and fieldX = 3, so basically cat_id = fieldX), just use a simple join. no need to use complicated if constructs. sql was built to do fast and efficient joins:

select a.field1, a.field2, sub.value
from abc a
left join sub_table sub
on a.fieldX = sub.cat_id

note however, that the second query will return multiple rows, when there are more matches between fieldX and cat_id (non-unique cat_id)

edited Jun 21, 2011 at 16:09

answered Jun 21, 2011 at 16:02

knittl

269k59 gold badges339 silver badges405 bronze badges

Comments

Bevilaqua · Accepted Answer · 2013-12-12 14:05:29Z

0

you could do something like:

select fields... from sub_table st
where st.idSubTable in(
Select IF(fieldX=3
,(
  select st.idSubTable from sub_table where cat_id = 3 order by id desc limit 0,1
 ),
  NULL)
 from abc);

it will solve your problem.

answered Dec 12, 2013 at 14:05

Bevilaqua

7701 gold badge6 silver badges13 bronze badges

Collectives™ on Stack Overflow

Mysql Select within IF condition

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related