Unselect the previous select

Question

I don't know it may sound weird... but here is my situation... I have to select rows from two tables (Table1) and (Table2)

Select * from Table1 Where <SomeCondition>
Select * from Table2 Where <SomeCondition>

But my constraint is If Table1 / Table2 return 0(zero) Rows... I should not return any results at all.

That ie... If 1st select returns say 10 rows and the 2nd select returns 0 (zero) rows, I should call back the first select also...

Is the Temp tables the only solution, or do we have any other alternative.

Thanks in advance for your response...

• Raja

Answer 1

One approach is to do an IF EXISTS first:

IF EXISTS(SELECT * FROM Table1 WHERE....) AND EXISTS(SELECT * FROM Table2 WHERE....)
    BEGIN
        -- Now do your SELECT on each, as both return results
    END

EXISTS should give good performance, as it will stop as soon as it does find a matching record.

Answer 2

Without more details of your specific queries, here is an option. As long as your queries aren't too complex aren't very intensive, this should work:

Select * from Table1 Where <SomeCondition>
where exists( Select null from Table2 Where <SomeCondition> );
Select null from Table2 Where <SomeCondition>
where exists ( Select null from Table1 Where <SomeCondition> );

This will only select rows in each statement if the other statement will also return any number of rows greater than zero.

Answer 3

An obvious but not-so-performant solution will be to count number of rows first (not sure about the syntax):

if not exists(select id from Table1 where ...) or not exists(select id from Table1 where ...)
    return

Select * from Table1 Where <SomeCondition>
Select * from Table2 Where <SomeCondition>

Answer 4

If you can use stored procedures you can use @@rowcount to check if the second query returned any results:

create proc pTest
as

Select * from Table1 Where <SomeCondition>

Select * from Table2 Where <SomeCondition>
if @@rowcount = 0 return

go