Mysql Select within IF condition

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 an开发者_运维问答d 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

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)

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)

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.