if condition in mysql select statement

Question

Please help to solve the following issue. I've table called time_schedule.
My Requirement is if start_city_name='somecity' then I need select departure_time
else I need to select arrival_time. Here I want to which one is selected. For ex:

SELECT IF(start_city_name='somecity',departure_time,arrival_time) 
   FROM time_schedule;

here if condition is matched departure_time is selected. Then I need to select it as departure_time.
else if condition is failed arrival_time is selected,then I need to select the result as arrival_time. I'm Using MySQL. Thanks in Advance.

Solution 1

To know which one is selected, you can do something like this:

SELECT IF(start_city_name='somecity', 'Departure time', 'Arrival time'),
       IF(start_city_name='somecity', departure_time, arrival_time)
FROM time_schedule;

You can't really have it as the column name, what if there's one row where the condition is true and one where the condition is false, what should the column name be?

However, if you're happy splitting them into 2 columns:

SELECT IF(start_city_name='somecity', NULL, arrival_time) AS 'Arrival time',
       IF(start_city_name='somecity', departure_time, NULL) AS 'Departure time'
FROM time_schedule;

This is very similar to simply saying:

SELECT arrival_time, departure_time
FROM time_schedule;

Except that arrival_time will be NULL when the condition is true, and departure_time will be NULL when the condition is false.

Solution 2

Use a CASE construct like this:

SELECT CASE start_city
         WHEN 'somecity' THEN departure_time
         ELSE arrival_time
       END AS column_alias
  FROM time_schedule;

Google for CASE statements for more details. There are plenty of resources on this.

Comments

[Srinivas]

Thanks @Dukeling. It is near to my requirement.But I want the column name as selected one based on condition. suppose departure_time is selected then in teh result column should be something like departure.

[Srinivas]

because I'm selecting more columns along with departure_time or arrival_time.

[Srinivas]

thanks @rachcha. My requirement is as follows. column_name:departure/arrival column_value:departure_time/arrival_time based on start_city_name column.

[Rachcha]

Do you mean 'departure_time' and 'arrival_time' are string literals and not column names?

[Srinivas]

those are actual column names in time_schedule table. based on start_city_name i need to select departure_time or arrival_time. and also I want to know which is selected one as alias column name in the result.

[Rachcha]

Well in such case @Dukeling's answer would suit better for your requirement.

[Srinivas]

see actually i've start_city_name,end_city_name columns which associates with one departure_time and arrival_time. If I choose start_city_name as my starting location then i need to select departure_time. Else if I choose start_city_name as my ending location then i need to select arrival_time in my result.

[Srinivas]

see actually i've start_city_name,end_city_name columns which associates with one departure_time and arrival_time. If I choose start_city_name as my starting location then i need to select departure_time. Else if I choose start_city_name as my ending location then i need to select arrival_time in my result adding the same scenario for end_city_name. The result should contain only two columns departure_time and arrival_time

[Rachcha]

So you will pass two variables to the query - something like start_or_end_city_name and start_or_end_location, is that right?

[Srinivas]

my table columns are: start_city_name, end_city_name,departure_time,arrival_time.

[Srinivas]

If I choose end_city_name column value for starting point and end point(these are parameter to my procedure). Then I need to check if startingpoint value is there in start_city_name then I need to get departure_time or if starting location is there in end_city_name then i need to get arrival_time. Same thing for endpoint parameter. Finally my result should have two columns arrival_time and departure_time

[Rachcha]

Ok so you are going to pass @start_location and @end_location. If @start_location = start_city_name then select departure_time, if @end_location = end_city_name then select arrival_time. Is that right?

[Srinivas]

yeah.. but little bit more logic. @start_location is not there in start_city_name then it will be there in end_city_name. In that case I need to select arrival_time

[Bernhard Barker]

@Srinivas It's a bit difficult to grasp what exactly you're trying to do. Can you edit your question with example input and output?