Select entries from the table, if a certain value exists, if not, select other entries

Question

I have a table like this:

projectName | info -------------------- all | i1 all | i2 all | i3 name1 | i4 name1 | i5 all | i6 

I have a query that validates a project name. If it exists in the table, I should select only information about this particular project. If this does not exist, I should get information for all projects.

For example, if my entry is "name1", my output should be:

 i4 i5 

If my entry is "name2", my output should be:

 i1 i2 i3 i6 

Is there a way to do this in a mysql query? I searched for examples, but all I found was getting information from two different tables.

Answer 1

One way is to use UNION ALL :

 SELECT info FROM mytable WHERE projectName = 'name1' UNION ALL SELECT info FROM mytable WHERE projectName = 'all' AND NOT EXISTS (SELECT 1 FROM mytable WHERE projectName = 'name1') 

Demo here

Answer 2

 select * from projects where projectName = case when exists (select * from projects where projectName = 'name1') then 'name1' else 'all' end