CODEWITHSUNDEEP
mysqlsqljoin
Duglas
Duglas

Reputation: 251

Join two queries into one

Is there a way to join this two queries into one?

query = "select foo from TABLE where foo like '%foo%'";

if (query.empty())
    query = "select bar from TABLE where bar like '%foo%'"

Update:

select ifnull(foo,bar) from TABLE where foo like 'foo%' or bar like '%foo%';

Thanks to Kamal for the idea

Upvotes: 6

Views: 255

Answers (5)

Diego
Diego

Reputation: 36126

   if not exists (select top 1 foo from TABLE where foo like '%foo%')
        select bar as MyColumn from TABLE where bar like '%foo%'
    else
        select foo as MyColumn from TABLE where foo like '%foo%'

Upvotes: 2

Bohemian
Bohemian

Reputation: 424973

Edited

I just realised that this can return multiple rows - here's the fix:

select foo from TABLE where foo like '%foo%'
union all
select bar from TABLE where bar like '%foo%'
and not exists (select 'x' from TABLE where foo like '%foo%')

Using UNION ALL (not UNION) will be faster, because UNION sorts the results.

Edited

A request has been made for a non-union solution. I don't have one.

Upvotes: 5

Kamal
Kamal

Reputation: 5522

For Oracle

Select NVL(foo,bar) from TABLE where foo like '%foo%' or bar like '%foo%';

Upvotes: 3

user359040
user359040

Reputation:

If you don't want bar returned where foo does return record(s), try:

select foo from TABLE where foo like '%foo%'
union all
select bar from TABLE
where bar like '%foo%' and 
      not exists (select null from TABLE where foo like '%foo%')

Alternatively, a version without a union:

select case when foo like '%foo%' then foo else bar end as foobar
where foo like '%foo%' or 
      (bar like '%foo%' and 
       not exists (select null from TABLE where foo like '%foo%'))

Upvotes: 2

Pranav
Pranav

Reputation: 8871

I do not know mysql syntax but in sql server we use like this :-

  IF EXISTS(select foo from TABLE where foo like '%foo%')
    BEGIN 
    select foo from TABLE where foo like '%foo%'
    END
    ELSE
    select bar from TABLE where bar like '%foo%

Upvotes: 1

Related Questions