Can SELECTING FROM one empty temp table in SQL cause the results to be empty?

Question

Here is my problem. I am creating 4 temp tables to count specific types of boxes and an employee's hours. Given a beginning date and ending date we want to know total boxes of each type (1, 2, and 3) and their total hours worked in that time period. All works perfectly if there is at least one of each type, but if only two types are present then I get a blank result for the entire final SELECT statement.

So, can a SELECT statement that contains an empty temp table in the FROM line cause everything else to return blank?

For example, the date range 6-1-10 to 6-10-10 returns 10 type 1 boxes, 12 type 2 boxes, 0 type 3 boxes, and 36 hours, but the result displayed is blank. But if it is extended one day and 15 type 3 boxes are included the query works.

SELECT Count(isnull(Box_Num,0)) as Box1, emp_num INTO #Box1
FROM  TEST.dbo.Prod_beta2
WHERE BoxType like '1' and time  > '06/01/10' + ' 12:01 AM'  and time  < '06/10/10' + ' 11:59pm' and emp_num like '10467'
group by emp_num


SELECT Count(isnull(Box_Num,0)) as Box2, emp_num INTO #Box2
FROM TEST.dbo.Prod_beta2
WHERE BoxType like '2' and time > '06/01/10' + ' 12:01 AM' and time < '06/10/10' + ' 11:59pm' and emp_num like '10467'
group by emp_num


SELECT count(isnull(box_num,0)) as Box3, emp_num INTO #Box3
from TEST.dbo.Prod_beta2
WHERE BoxType like '3' and time > '06/01/10' + '  12:01 AM' and time < '06/10/10' + ' 11:59pm' and emp_num like '10467'
group by emp_num


SELECT SUM(HOURS) as TotalHours, empid INTO #Hours
FROM TEST.dbo.Timeclock
where timein > '06/01/10' + ' 12:01 AM' and timein < '06/10/10' + ' 11:59pm' and empid like '10467'
group by empid


SELECT Box1, Box2, Box3, TotalHours
FROM #Box1, #Box2, #Box3, #Hours


DROP TABLE #Box1, #Box2, #Box3, #Hours

Answer 1

If the fields are all of the same type then you should not do it like this:

SELECT Box1, Box2, Box3, TotalHours 
FROM #Box1, #Box2, #Box3, #Hours

You should use a UNION ALL query

Like so:

   SELECT Count(isnull(Box_Num,0)) as Box1, emp_num
    FROM  TEST.dbo.Prod_beta2 
    WHERE BoxType like '1' and time  > '06/01/10' + ' 12:01 AM'  and time  < '06/10/10' + ' 11:59pm' and emp_num like '10467' 
    UNION ALL
    SELECT Count(isnull(Box_Num,0)) as Box2, emp_num
    FROM TEST.dbo.Prod_beta2 
    WHERE BoxType like '2' and time > '06/01/10' + ' 12:01 AM' and time < '06/10/10' + ' 11:59pm' and emp_num like '10467' 
    UNION ALL

SELECT count(isnull(box_num,0)) as Box3, emp_num
from TEST.dbo.Prod_beta2
WHERE BoxType like '3' and time > '06/01/10' + '  12:01 AM' and time < '06/10/10' + ' 11:59pm' and emp_num like '10467'
group by emp_num

Your last select is the only thing that differs so just separate that query from the number of boxes being displayed.

However, you do not even need temp tables for this just use the standard SELECT statements and add union all to each of them.

Your current statement will yield 0 rows provided any one of those queries contains 0 rows. Look into cartesian products and what they mean

Answer 2

Yes. You are doing a Cartesian product which means you will have B1 * B2* B3 * H rows in the result.

When B3 has no rows you are obviously multiplying by zero.

The first three of your conditions can easily be combined (by the way what is the data type of BoxType and emp_num? If they are numeric then don't use strings to avoid any unnecessary type conversion issues. Additionally your date logic seems to miss anything between 11:59pm to 12:01 AM. Is this intentional? Moreover you should probably use ISO date formats to avoid any issues if you ever change servers etc. )

SELECT 
Count(CASE WHEN BoxType = '1' THEN 1 ELSE NULL END) as Box1,
 Count(CASE WHEN BoxType = '2' THEN 1 ELSE NULL END) as Box2,
 Count(CASE WHEN BoxType = '3' THEN 1 ELSE NULL END) as Box3
FROM  TEST.dbo.Prod_beta2
WHERE BoxType in ('1','2','3') and time  > '06/01/10' + ' 12:01 AM'  and 
  time  < '06/10/10' + ' 11:59pm' and emp_num like '10467'
group by emp_num