jalal aga jalal aga - 3 years ago 89
MySQL Question

Either or select in a single sql query

I am beginner in sql world and hope I manged to put the question statement correctly.
Ok my question is how to create a mutual exclusive mysql query where the results are based on the condition.
For example : table

id name
1 tom
2 dik
3 harry
4 mike
5 john

if ask for "john" select should give ONLY record of john , if john not found then give all remaining records.

I am struggling to imagining how the exclusivity will be work.
My tries are :

select case
when if exists select 1 from emp where emp_name = 'john'
then select emp_name from emp where emp_name = 'john'
else select emp_name from emp

SELECT COALESCE(select emp_name from emp where emp_name = 'john',select emp_name from emp)

select distinct(e.emp_name) from emp e left outer join emp f where e.emp_name = 'john' OR e.emp_name = f.emp_name;

ofcourse none of them are working..
My questions
: How to approach such queries?
: Can we build if-then in sqls? will work row by row or on filtered results?
: If this is already answered anywhere, would be more than happy to have the link.

Answer Source

Something like this:

select emp_name
from emp
where emp_name = 'john'
union all
select emp_name
from emp
where not exists (select 1 from emp where emp_name = 'john');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download