SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Advanced joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidais
Starting Member

17 Posts

Posted - 10/03/2012 :  07:08:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  07:32:14  Show Profile  Reply with Quote
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
....
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 10/03/2012 :  08:32:08  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  08:39:59  Show Profile  Reply with Quote
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;    
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 10/03/2012 :  10:52:33  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000