MySQL IF clause Issue

Question

mysql> SELECT
   -> IF(status='EndSP',reportId,'') as 'reportId_EndSP'
   -> FROM T_Name LIMIT 10;
+--------------+
| reportId_EndSP |
+--------------+
|                |
|                |
| 270241         |
|                |
| 270242         |
|                |
|                |
| 270244         |
|                |
|                |
+--------------+
10 rows in set (0.00 sec)

But i need to return only the row where status = 'EndSP'

I don't need the else clause to be executed.

I can simply achieve that by writing

SELECT reportId FROM T_Name where status='EndSP';

but i need to do it using if or case.

EDIT/UPDATE

My actual query looks like

SELECT
   -> IF(status='EndSP',reportId,'') as 'reportId_EndSP',
   -> IF(status='EndSP',createdTS,'') as 'createdTS_EndSP',
   -> IF(status='BeginSP',reportId,'') as 'reportId_BeginSP',
   -> IF(status='BeginSP',createdTS,'') as 'createdTS_BeginSP'
   -> FROM T_Name HAVING reportId_EndSP!='' AND reportId_BeginSP!='' LIMIT  10;

xception · Accepted Answer · 2012-10-09 10:11:30Z

2

How about:

SELECT * FROM (
   SELECT
      IF(status='EndSP',reportId,'') as reportId_EndSP
      FROM T_Name) a
WHERE reportId_EndSP != '' LIMIT 10;

demo http://sqlfiddle.com/#!2/d1167/8

or the single select version

SELECT
   IF(status='EndSP',reportId,'') as reportId_EndSP
   FROM T_Name
HAVING reportId_EndSP != '' LIMIT 10;

demo http://sqlfiddle.com/#!2/d1167/9

answered Oct 9, 2012 at 10:11

xception

4,3171 gold badge20 silver badges29 bronze badges

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

3 Comments

JP Chauhan Over a year ago

Thanks for your answer +1.....Have a look at my edit/update part...I am getting 0 rows in resuLt set.

xception Over a year ago

How about using OR instead of AND in having, it is physically impossible for status to be equal both EndSP and BeginSP at the same time for a single row!

xception Over a year ago

Simplified fiddle sqlfiddle.com/#!2/d1167/16 , removed the rows I didn't have and renamed BeginSP to StartSP - but it should prove the point

Mihai Stancu · Accepted Answer · 2012-10-09 10:01:02Z

1

You can't use only IF/CASE because they do not filter out the values from the result set, when the IF or CASE is called, the result set is already decided.

answered Oct 9, 2012 at 10:01

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

2 Comments

JP Chauhan Over a year ago

So is there any other way to do that withot using where clause..?

Mihai Stancu Over a year ago

You can use a HAVING clause but it'd be peculiar. You could filter out the results in the programming language where they'll be received. Or you could use a select over the primary select and add a WHERE clause there.

Fathah Rehman P · Accepted Answer · 2012-10-09 10:08:08Z

0

SELECT
 reportId as 'reportId_EndSP'
 FROM T_Name where  IF(status='EndSP',reportId,' ') LIMIT 10;

answered Oct 9, 2012 at 10:08

Fathah Rehman P

8,8014 gold badges43 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL IF clause Issue

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related