CODEWITHSUNDEEP
mysqlsql
Sepideh
Sepideh

Reputation: 133

how can write sql query statement with if-else

I want to to select from db 

if id=0 select * from tbl
else select * from tbl where id= :id

how can use it in mysql query?

Upvotes: 1

Views: 110

Answers (5)

XWiśniowiecki
XWiśniowiecki

Reputation: 1843

I think you should create function that takes your id as a parameter and then you can use mySql IF statement. Here you have simple and quick tutorial about IF statement and also example of function IF statement.

Be aware that mySql has build-in IF() function also.

Upvotes: 0

ntalbs
ntalbs

Reputation: 29448

Try this:

select * from tbl where :id = 0
union all
select * from tbl where :id <> 0 and id = :id

This is just a single query and it will execute only one branch as the specified :id value. When :id=0, the first query's where condition become true and the result is the same as select * from tbl. When :id<>0, the result of the first query will be empty, however, the second query will return the result of select * from tbl where id=:id.

Upvotes: 2

Rimas
Rimas

Reputation: 6024

SELECT *
  FROM tbl
  WHERE id = :id OR 0 = :id

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

The difference between both your queries is just the where clause - you can express this with the or logical operator:

SELECT * 
FROM   tbl
WHERE  (:id = id) OR (:id = 0)

Of course, this could be further cleaned up with the in operator:

SELECT * 
FROM   tbl
WHERE  :id IN (id, 0)

Upvotes: 2

nesreen
nesreen

Reputation: 201

if (id=0)  then 
select * from tbl
else 
select * from tbl where id= :id
end if;

Upvotes: 1

Related Questions