CODEWITHSUNDEEP
mysql
Bala
Bala

Reputation: 53

if with yes or no status mysql query

I have 2 tables, the first table or_f_table data. The second table or_table

or_f_table
f_id   | f_o_id  | f_u_id
1      |    19   |    1
2      |    5    |    2
3      |    19   |    2

or_table
o_id  | o_name
  4   |  test1
  5   |  test2
  19  |  oops2
  20  |  oops3


SELECT o.o_name,
IF ((SELECT count(*)  FROM or_f_table as f 
WHERE f.f_u_id = 1 ),'Yes','No') as follow_status
FROM or_table as o  
WHERE o.o_name LIKE '%oop%'

I want to do something like this result :- 

o_name   |  follow_status
oops2    |  Yes
oops3    |  No

I am getting result 

o_name   |  follow_status
oops2    |  Yes
oops3    |  Yes

Why doesn't it work? And how should I correct it

Upvotes: 0

Views: 231

Answers (1)

A J
A J

Reputation: 4024

There will always be a value greater than 0 for your where condition. That is why it is not working.

Try this to get the specified results

    SELECT o.o_name,
     IF ((SELECT count(*)  FROM or_f_table as f 
     WHERE f.f_o_id = o.o_id ),'Yes','No') as follow_status
     FROM or_table as o  
     WHERE o.o_name LIKE '%oop%'

Upvotes: 1

Related Questions