Alternative to Left Join

问题:

I need to display the monthly rates for a fixed set of pipelines in Excel 2007 using MS Query and even if a pipeline has no monthly rate it has to be displayed in this manner

required output http://i42.tinypic.com/29j615.jpg

I have done it using the following code in SQL Server 2008 R2.

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
FROM   [GAS].[dbo].[Pipelinerate] PR 
       INNER JOIN Pipeline P 
               ON P.Id = PR.Pipelineid 
       LEFT OUTER JOIN [GAS].[dbo].[Pipelineratehistory] PH 
               ON PH.[Pipelinerateid] = PR.[Id] 
               AND ( PH.[Month] = ?   --Month
                     AND PH.[Year] = ? )  --Year
WHERE  ( PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) ) 
       AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
ORDER  BY [Pipeline name], 
          PR.[Name] 

When I try to do it in MS Query in Excel 2007 then I get I get the following errors

[Microsoft][ODBC SQL Server Driver] Invalid Parameter Number

[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index

I found out by trail and error that If I remove the conditions with the parameters from the ON clause and put it in WHERE Clause as below

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
FROM   [GAS].[dbo].[Pipelinerate] PR 
       INNER JOIN Pipeline P 
               ON P.Id = PR.Pipelineid 
       LEFT OUTER JOIN [GAS].[dbo].[Pipelineratehistory] PH 
               ON PH.[Pipelinerateid] = PR.[Id] 
WHERE  ( PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) ) 
       AND ( PH.[Month] = ?   --Month
             AND PH.[Year] = ? )  --Year
       AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
ORDER  BY [Pipeline name], 
          PR.[Name] 

The code works in MS Query and give the following result

actual output http://i42.tinypic.com/5qiog.jpg

In this output the pipelines with no rates for the month are not shown. Hence this code doesn't work. Thus I am trying to find alternatives to LEFT JOIN in this case to get the desired output in excel using MS Query.

Associations
Pipeline and PipelineRate - optional one to many relationship
PipelineRate and PipelineRateHistory - optional one to many relationship

Can anyone suggest alternatives to left join or a way to accomplish this?

PS: I can't use stored procedures. I know how to do this using VBA. I require to accomplish this using MS Query

回答1:

Try:

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
FROM   [GAS].[dbo].[Pipelinerate] PR 
       INNER JOIN Pipeline P 
               ON P.Id = PR.Pipelineid 
       LEFT OUTER JOIN [GAS].[dbo].[Pipelineratehistory] PH 
               ON PH.[Pipelinerateid] = PR.[Id] 
               AND PH.[Month] = ?   --Month
                     AND PH.[Year] = ? --Year
WHERE  ( PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) ) 
       AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
ORDER  BY [Pipeline name], 
          PR.[Name] 

All I did was remove some unnecessary parenthesis. Apparently there is a bug with Excel. One of the workarounds is to turn that into a stored procedure and call the stored proc from Excel. From reading it appears to be possibly a problem with how the parameters are determined and numbered.

回答2:

Perhaps it's just ODBC unsuccessfully trying to rewrite your query. I don't know why the ON version doesn't work, but you just have to include the nulls in the WHERE. Nulls doesn't equate to anything, so the ='s are dropping the rows with nulls.

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
  FROM [GAS].[dbo].[Pipelinerate] PR 
 INNER JOIN Pipeline P 
    ON P.Id = PR.Pipelineid 
  LEFT OUTER JOIN [GAS].[dbo].[Pipelineratehistory] PH 
    ON PH.[Pipelinerateid] = PR.[Id] 
 WHERE PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) 
   AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
   AND (PH.[Month] IS NULL OR
        (PH.[Month] = ?   --Month
     AND PH.[Year] = ? )  --Year
        )
ORDER  BY [Pipeline name], 
          PR.[Name] 

回答3:

As per your first query u tried to put month filter in the left join. Since that is a left join your getting records The month and Year filter is not mandatory.

But in next query u put the filter in where condition so the query is not returning any result for you.

If your month and year filter is mandatory please put it in where and the second query is giving correct number of results.

Or else please use this query

SELECT P.Name     AS [Pipeline Name], 
       PR.Id, 
       PR.[Name], 
       PH.[Value] AS Rate 
FROM   [GAS].[dbo].[Pipelinerate] PR 
       INNER JOIN Pipeline P 
               ON P.Id = PR.Pipelineid 
       LEFT OUTER JOIN (select [Pipelinerateid],[Value] 
                       from [GAS].[dbo].[Pipelineratehistory] 
                       where [Month] = ?   --Month
                     AND [Year] = ? --Year
                      ) PH 
               ON PH.[Pipelinerateid] = PR.[Id] 
       WHERE  ( PR.[Id] IN ( 197, 198, 1, 2, 3, 5, 67, 68, 23 ) ) 
               AND ISNULL(PH.Deprecated, 'n') <> 'Y' 
ORDER  BY [Pipeline name], 
          PR.[Name]