How to choose based on different column data

Question

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

An example would 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 data where the schedule is on, and then look at the beginning and end of the date. Because if the schedule is not included, it makes no sense to study the dates. Just select the data. With a schedule enabled, I want to be more selective when choosing scheduled data.

I am trying to find out if the MySQL CASE or IF statements work, but cannot do this. How to run this selection?

Thanks.

Answer 1

You can use UNION to mix and match the results of two different SQL queries into a single 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 

Please note that both requests must have compatible output fields (the same number and type for operation). Using UNION automatically merges only individual records - if you want to keep duplicate results, use UNION ALL .

In this particular case, the more extensive WHERE -clause will also work:

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

But given the question, I assume that your real case is a little more complicated.

The documentation on the MySQL site .

Answer 2

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 

Thus, he selects only schedule 0 OR 1 with a date greater than or equal to now; I used DATE (NOW ()) to remove a time that you are not interested in.