Select records from table if certain value exists, if not, select other records

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 checks the project name. If it exists in the table, I have to select only the information regarding that specific project. If it does not exist, I must get the information for 'all' projects.

For example, if I 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 I can do this in a mysql query? I looked for examples but everything I found was about retrieving information from two different tables.

Why do you need to do this in a MySQL query? This would probably be easier to do within your application. — Jefferson Lima
– Jefferson Lima, Commented Apr 18, 2017 at 14:15
@JeffersonLima I just got curious about it, actually. I'll do it within my application if I can't work this out. — Bizzys
– Bizzys, Commented Apr 18, 2017 at 14:43

Giorgos Betsos · Accepted Answer · 2017-04-18 14:13:28Z

4

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

answered Apr 18, 2017 at 14:13

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Spiegel · Accepted Answer · 2017-04-18 14:19:02Z

3

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

answered Apr 18, 2017 at 14:19

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

3 Comments

dsharew Over a year ago

but you are outputing the project name instead of project info

Paul Spiegel Over a year ago

@dsharew I'm outputting all columns select *. You can still select any column you want from the table.

dsharew Over a year ago

got it very nice solution

Collectives™ on Stack Overflow

Select records from table if certain value exists, if not, select other records

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related