CODEWITHSUNDEEP
mysql
Dheeraj Nayak
Dheeraj Nayak

Reputation: 161

how to use IF ELSE conditions in mysql

Table: mytable

id  created_on            status    purchase_price_without_tax  purchase_tax
1   2015-08-07 00:00:00     1              3322.80                 5.00
1   2015-07-15 15:16:23     7              3599.00                 71.98
1   2015-07-14 15:16:23     2              3599.00                 71.98
1   2015-07-16 15:16:23     4              3599.00                 91.98
1   2015-07-17 15:16:23     5              3599.00                 21.98

here is my query

SELECT * FROM mytable 

WHERE 
(IF( `status` IN('1','2') , (`status` IN('1','2')),  (`status` IN('7')) )  )
AND id=1

But not getting proper result, because I would like it to be like this:

where 
IF (`status`=1 OR  `status`=2 )
      then status IN ('1','2')

ELSE
   `status`=7

Note : If status is 1 or 2 then it will left all other status row and select only those rows which have status 1 OR 2 . If there is no rows which have status as 1 or 2, then select only those rows which have status = 7

Upvotes: 2

Views: 234

Answers (1)

Barmar
Barmar

Reputation: 780869

Use a UNION of two queries that test the two cases.

SELECT *
FROM mytable
WHERE status IN (1, 2)
AND EXISTS (SELECT 1 FROM mytable WHERE status IN (1, 2))
UNION
SELECT *
FROM mytable
WHERE status = 7
AND NOT EXISTS (SELECT 1 FROM mytable WHERE status IN (1, 2))

Upvotes: 1

Related Questions