CODEWITHSUNDEEP
sqlsql-servervb.nett-sql
cost
cost

Reputation: 4480

Two SQL statements, ignore the return of the first if it has no results?

I have a sql statement SELECT * FROM table1 WHERE ....; SELECT * FROM table2 WHERE ....

What I want is to get the results from the first select statement if it returns results, but if it doesn't, I want to ignore it and just get the results from the second select statement. Is there a way I can do this just using SQL?

I'm getting this returned to me as a datatable, using a dataadapter to fill the datatable from the above SQL statement. I can't change that part, or switch to filling a dataset (for reasons I won't get into, but it just can't be changed).

Upvotes: 1

Views: 548

Answers (2)

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

A couple options. You can check the count first:

If (select count(*) from table1 where...) > 0
begin
    select * from table1 where...
end
else
begin
    select * from table2 where...
end;

if both result sets are identical in structure, you can save the count check (and thus improve performance) by using a temp table:

create table #temp (col1 int, col2 varchar(10), ...);

insert #temp
select * from table1 where...;

if @@rowcount = 0 
begin
    insert #temp
    select * from table2 where...
end;

select * from #temp;

Upvotes: 0

user359040
user359040

Reputation:

Assuming both queries return the same number and type of columns, one way to do this would be:

select * from table1 where ... /* query 1 conditions */
union all
select * from table2 where ... /* query 2 conditions */
and not exists
(select 1 from table1 where ... /* query 1 conditions */)

Upvotes: 3

Related Questions