LEFT JOIN on Inline Tables

Google hasn't helped me here nor has Micro开发者_如何学Pythonsoft online help yet.

I have Inline tables which are generated as a subquery. (See simplified code below, I have several more inline tables.)

Now, this is fine where I have data. However, there are cases where I need to return results when there isn't data. For example, Inline table 1 returns my number of active customers... If I specifiy a range where there are no active customers I get no results for the whole query.

This is due to my join (AND IL1.transaction_id = th.transaction_id)

How can I left join the inline table?

I tried LEFT JOIN IL1 on il1.transaction_id = th.transaction_id but it says the table doesn't exist.

select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
            IL1.Active as 'Number of Active Customers TY',
            COUNT(th.transaction_id) as 'Number of Transactions TY'

FROM        

(SELECT transaction_type, COUNT(DISTINCT customer_id) as 'Active' from transaction_header  
where transaction_date BETWEEN @Active and @ToDate group by transaction_type)IL1, 
transaction_header th

INNER JOIN transaction_type tt ON th.transaction_type = tt.transaction_type
WHERE 
th.transaction_date Between @FromDate AND @ToDate

AND         IL1.transaction_type = th.transaction_type

GROUP BY 
            tt.transaction_type_description, IL1.Active

Any help is really appreciated.

Since you're running SQL Server 2005, I'm going to use a CTE to clean this up a bit.

;with cteIL1 as (
    SELECT transaction_type, COUNT(DISTINCT customer_id) as 'Active' 
        from transaction_header  
        where transaction_date BETWEEN @Active and @ToDate 
        group by transaction_type
)
select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
        ac.Active as 'Number of Active Customers TY',
        COUNT(th.transaction_id) as 'Number of Transactions TY'
    FROM transaction_header th
        INNER JOIN transaction_type tt 
            ON th.transaction_type = tt.transaction_type
        LEFT JOIN cteIL1 IL1
            on th.transaction_type = IL1.transaction_type
    WHERE th.transaction_date Between @FromDate AND @ToDate
    GROUP BY tt.transaction_type_description, IL1.Active     

EDIT: Non-CTE version for 2000 as mentioned in comments:

select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
        ac.Active as 'Number of Active Customers TY',
        COUNT(th.transaction_id) as 'Number of Transactions TY'
    FROM transaction_header th
        INNER JOIN transaction_type tt 
            ON th.transaction_type = tt.transaction_type
        LEFT JOIN (SELECT transaction_type, COUNT(DISTINCT customer_id) as 'Active' 
                       from transaction_header  
                       where transaction_date BETWEEN @Active and @ToDate 
                       group by transaction_type
                   ) IL1
            on th.transaction_type = IL1.transaction_type
    WHERE th.transaction_date Between @FromDate AND @ToDate
    GROUP BY tt.transaction_type_description, IL1.Active       

You shouldn't mix implicit and explicit joins, you may get inconsistent results. Also well frankly you should never use an implicit join.

See if this works for you:

SELECT  SUM(th.total_net_retail_central) AS 'Net Purchases  TY',  
            COALESCE(IL1.Active, 0) AS 'Number of Active Customers TY', 
            COUNT(th.transaction_id) AS 'Number of Transactions TY' 

FROM     transaction_header th      
INNER JOIN transaction_type tt 
    ON th.transaction_type = tt.transaction_type 
LEFT JOIN (SELECT transaction_type, COUNT(DISTINCT customer_id) AS 'Active' 
            FROM transaction_header   
            WHERE transaction_date BETWEEN @Active and @ToDate 
            GROUP BY transaction_type)IL1
    ON  IL1.transaction_type = th.transaction_type 
WHERE  th.transaction_date BETWEEN @FromDate AND @ToDate 
GROUP BY  tt.transaction_type_description, COALESCE(IL1.Active, 0)

select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
            IL1.Active as 'Number of Active Customers TY',
            COUNT(th.transaction_id) as 'Number of Transactions TY'
FROM        
(SELECT transaction_type, 
       COUNT(DISTINCT customer_id) as 'Active' 
         from transaction_header  
    where transaction_date BETWEEN @Active and @ToDate 
     group by transaction_type ) IL1
     right join
                transaction_header th
                 on IL1.transaction_type = th.transaction_type
INNER JOIN transaction_type tt 
            ON th.transaction_type = tt.transaction_type
WHERE 
th.transaction_date Between @FromDate AND @ToDate
GROUP BY 
            tt.transaction_type_description, IL1.Active

If I am reading this correctly I believe that you need a RIGHT JOIN between IL1 and TH

IL1 right join transaction_header th on IL1.transaction_type = th.transaction_type