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.

Answer 1

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.