Advanced joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Advanced joins

Author

Topic

davidais
Starting Member

17 Posts

Posted - 2012-10-03 : 07:08:48

Hi all,

I am trying to join two columns [AccountNo] and [SubAddress] from two tables [tbOrderHistory] and [dbo.uvTemp_PP_Projects].

The columns need to be specified as left joins from [tbOrderHistory] as both [AccountNo] and [SubAddress] are unique to the [ProjectName] in [dbo.uvTemp_PP_Projects].

When joining I had to use correlation names to distinguish between them [PP1] and [PP2].

The result produces values that are either unique to [PP1] or [PP2] depending on which one I select - however I need the unique [ProjectName] based on the combination of both joins.


SELECT
tbGRNHistory.SupplierAccNo, tbGRNHistory.GRNInDate, tbGRNHistory.PONumber, tbOrderHistory.SONumber, tbOrderHistory.AccountNo as CustAccNo, tbCustomers.CustomerName, tbOrderHistory.SubAddressCode, PP2.ProjectName, tbGRNHistory.Value

FROM
tbGRNHistory
left join tbPOrderHistory on tbGRNHistory.PONumber=tbPOrderHistory.PONumber
left join tbOrderHistory on tbPOrderHistory.SONumber=tbOrderHistory.SONumber
left join tbCustomers on tbOrderHistory.AccountNo=tbCustomers.AccountNumber
Left join dbo.uvTemp_PP_Projects as PP1 on tbOrderHistory.AccountNo=PP1.AccountNo
Left join dbo.uvTemp_PP_Projects as PP2 on tbOrderHistory.SubAddressCode=PP2.SubAddress

GROUP BY
tbGRNHistory.SupplierAccNo, tbGRNHistory.GRNInDate, tbGRNHistory.PONumber, tbOrderHistory.SONumber, tbOrderHistory.AccountNo, tbCustomers.CustomerName, tbOrderHistory.SubAddressCode, tbGRNHistory.Value, PP2.ProjectName

Many thanks,

D

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 07:32:14

Add another column to your select list using coalesce:

SELECT
COALESCE(pp1.ProjectName,pp2.ProjectName) AS TheProjectName,
... your other columns
FROM
tbGRNHistory
left join tbPOrderHistory on tbGRNHistory.PONumber=tbPOrderHistory.PONumber
left join
....

davidais
Starting Member

17 Posts

Posted - 2012-10-03 : 08:32:08

thanks but I'm afraid this does not work,

[ProjectName] can have both multiple instances of [AccountNo] and [SubAddress], I need to the unique combination of these two fields in the results

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 08:39:59

Let us see if second time is the charm :) If the following does not work, can you post some representative sample data? In the code below, you have to specify some ordering criterion (see "SomeColumn" in the query) and it will pick the first row based on that ordering criterion.

;WITH cte AS
(
SELECT 
	ROW_NUMBER() OVER (PARTITION BY COALESCE(pp1.ProjectName,pp2.ProjectName) ORDER BY SomeColumn) AS RN,
		tbGRNHistory.SupplierAccNo,
       tbGRNHistory.GRNInDate,
       tbGRNHistory.PONumber,
       tbOrderHistory.SONumber,
       tbOrderHistory.AccountNo          AS CustAccNo,
       tbCustomers.CustomerName,
       tbOrderHistory.SubAddressCode,
       PP2.ProjectName,
       tbGRNHistory.Value
FROM   tbGRNHistory
       LEFT JOIN tbPOrderHistory
            ON  tbGRNHistory.PONumber = tbPOrderHistory.PONumber
       LEFT JOIN tbOrderHistory
            ON  tbPOrderHistory.SONumber = tbOrderHistory.SONumber
       LEFT JOIN tbCustomers
            ON  tbOrderHistory.AccountNo = tbCustomers.AccountNumber
       LEFT JOIN dbo.uvTemp_PP_Projects  AS PP1
            ON  tbOrderHistory.AccountNo = PP1.AccountNo
       LEFT JOIN dbo.uvTemp_PP_Projects  AS PP2
            ON  tbOrderHistory.SubAddressCode = PP2.SubAddress
GROUP BY
       tbGRNHistory.SupplierAccNo,
       tbGRNHistory.GRNInDate,
       tbGRNHistory.PONumber,
       tbOrderHistory.SONumber,
       tbOrderHistory.AccountNo,
       tbCustomers.CustomerName,
       tbOrderHistory.SubAddressCode,
       tbGRNHistory.Value,
       PP2.ProjectName
)
SELECT * FROM cte WHERE RN=1;

davidais
Starting Member

17 Posts

Posted - 2012-10-03 : 10:52:33

perhaps this will help explain a little differently - when it is done in Access the code looks like this:


LEFT JOIN dbo_uvTemp_PP_Projects ON (dbo_uvOrderHistoryLatest.AccountNo = dbo_uvTemp_PP_Projects.AccountNo) AND (dbo_uvOrderHistoryLatest.SubAddressCode = dbo_uvTemp_PP_Projects.SubAddress)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources