Tutorial :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


Solution: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.


Solution: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.


Solution: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>  


Solution: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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »