mrgunston mrgunston - 4 months ago 12
MySQL Question

How can I select from the results of another select in MySQL?

I have a query to select all records from a table. There is a status field, so each record has a particular status. I want to select all records regardless of the status. There is another field called time however, I only want to select this field if the status = "A".

So the below query will show all the data

Select field1, field2, field3, status, time from table1


However, I need the query to be more like this:

Select field1, field2, field3, status, time(where status = "A") from table1


I want to see all other records regardless of the status however, I want NULL or black in the time field where the status is A.

Answer

Couple options, here's a generic approach with case:

Select field1, field2, field3, status, 
    case when status = 'A' then 'black' end as `time`
from table1