Mysql Question posted in
The official MySQL documentation can be found here.

Running one MySQL query based on 2 conditions

I have a table with various columns in it. I want to run a query on this table that is defined by 2 conditions that rely on 2 parameters,

  1. When column_a has a value that is non zero, then set the where condition to be column_a = param_1
  2. When column_a has a value that is zero, then set the where condition to be column_b = param_2

My first attempt at this was the following:

Select * from mytable where column_a = param_1 or column_b = param_2

There is a relation between column_a and column_b in that when column_a is non zero then it will not require the latter part of the query (or column_b = param_2) but when column_a is zero then I only want to run the latter part of the query and exclude the first part (column_a = param_1).

Is something like this possible?

Tags:

2

Answers


  1. 0

    You have to add in a test for whether or not column_a is zero. Then check the appropriate param in each case

    Select * from mytable 
where 
(column_a = param_1 or column_a = 0) 
and 
(column_b = param_2 or column_a <> 0)

    If a is zero, then the first clause is always true

    If a is nonzero then the second clause is always true

    Login or Signup to reply.
  2. 0

    If you need the query to be fast then think about two separate queries combined with UNION ALL:

    SELECT * FROM table WHERE column_a = @param_1 AND column_a <> 0
UNION ALL
SELECT * FROM table WHERE column_b = @param_2 AND column_a = 0

    If @param_1 cannot be zero then the condition AND column_a <> 0 can be removed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search