How to select based on different column data

Question

I want to perform a different SELECT based on the column data. For example I have a table http://sqlfiddle.com/#!2/093a2 where I want compare start_date and end_date only if use_schedule = 1. Otherwise select all data. (A different select) Basically I only want to compare the start and end date if only use_schedule is 1 and if use_schedule is 0 then select rest of the data.

An example may be something like

select id, name from table
where use_schedule = 0 
else 
select id, name, start_date from table 
where use_schedule = 0 and current_date >= start_date.

Basically I have the data where schedule is enabled only then look into start and end date. Because if schedule is not enabled there is no point of looking into the dates. Just select the data. With schedule enabled, I want to be more selective in selecting the scheduled data.

I am trying to figure out if MySQL CASE or IF statements would work but not able to do so. How can I run this select?

Thanks.

What do you mean you want to compare start_date and end_date? IF the start if after the end? The start is before? please give more data. — Mr.Web, Jul 06 '13 at 08:22
Basically I have the data where schedule is enabled only then look into start and end date. Because if schedule is not enabled there is no point of looking into the dates. Just select the data. With schedule enabled, I want to be more selective in selecting the scheduled data. — NBhatti, Jul 06 '13 at 08:38
OK, now I get the question better, add this to the question so that it's better explained. I see you put "mysql" only as a tag, are you using a server-side language as well? — Mr.Web, Jul 06 '13 at 08:47
I am using LUA, performing this operation outside MySQL is no big deal. I am just trying to cut 1 query out. — NBhatti, Jul 06 '13 at 08:51

Mr.Web · Answer 1 · 2013-07-06T09:20:34.150

1

Use CASE, in this case..:

SELECT id, name, 
   (CASE 
        WHEN start_date >= DATE(NOW()) AND use_schedule = 1 
        THEN start_date 
    ELSE NULL 
    END) AS cols FROM campaigns

This way it selects only the schedule 0 OR the 1 with a date bigger or equals to now; I used DATE(NOW()) so that it removes the time which you are not interested in.

edited Jul 06 '13 at 09:20

answered Jul 06 '13 at 08:34

Mr.Web

6,992
8
51
86

score 1 · Accepted Answer · answered Jul 06 '13 at 09:10

You can use UNION to mix and match the results of 2 different SQL queries into one result set:

select id, name, null from table
where use_schedule = 0 
union 
select id, name, start_date from table 
where use_schedule = 1 and current_date >= start_date

Note that both queries have to have compatible output fields (same number and type for this to work). The use of UNION automatically merges only distinct records - if you want to keep double results use UNION ALL instead.

In this specific case a more extensive WHERE-clause would also work obviously:

where use_schedule = 0 or (use_schedule = 1 and current_date >= start_date)

But given the question I'm assuming your real case is a bit more complex.

Documentation over at MySQL site.

Union is actually two SELECTs, which is I am trying to avoid. If 2 SELECTS is the option, I could have done that in my programming language. Your solution works perfect, but let's see if we can figure out something more efficient? — NBhatti, Jul 06 '13 at 19:09
A union of 2 selects is far more efficient than 2 separate selects internally. — Niels Keurentjes, Jul 08 '13 at 08:49

How to select based on different column data

2 Answers2