Attach two queries to one

Question

Is there a way to combine these 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 Kamal for the idea

Answer 1

For Oracle

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

Answer 2

Edited

I just realized that this could return multiple lines - 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

Request for a non-union solution. I don’t have it.

Answer 3

If you do not want bar return to where foo returns entries, 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, the version without merging:

 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%')) 

Answer 4

  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%' 

Answer 5

I do not know the mysql syntax, but in the sql server we use 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%