Solved

Complex Left Join

Posted on 2010-11-30
10
414 Views
Last Modified: 2012-05-10
I need to do a left join between my two attached select statements on

First Select                             Second Select
SubString(afsSource,4,4)  =  DealID
Select  Left(afsSource,2) as busType,  

        SubString(afsSource,4,4) as DealID,

		Sum(afsAMount) as afsAmount       

from    proc_cfa.dbo.P_AvailableForSale  

where   afsTransAction = @OrdTranID  

group by Left(afsSource,2), SubString(afsSource,4,4)





Select            

        DealID  

        ,CASE WHEN COUNT(PmtFrequency) > 1 THEN 'Split' ELSE MIN(PmtFrequency) END cp  

        ,MAX(loanDate) LoanDate

from    proc_lt.dbo.P_PaymentStreams 

Group by DealID 

order by DealID

Open in new window

0
Comment
Question by:lrbrister
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 4

Expert Comment

by:patterned
Comment Utility
0
 
LVL 32

Accepted Solution

by:
Erick37 earned 350 total points
Comment Utility
You could use a CTE (connon table expression) to make it easier:
;WITH cte_PaymentStreams (DealID,cp,LoanDate) AS

(

	Select            

			DealID  

			,CASE WHEN COUNT(PmtFrequency) > 1 THEN 'Split' ELSE MIN(PmtFrequency) END cp  

			,MAX(loanDate) LoanDate

	from    proc_lt.dbo.P_PaymentStreams 

	Group by DealID 

	order by DealID

)

Select  Left(afsSource,2) as busType,  

        SubString(afsSource,4,4) as DealID,

		Sum(afsAMount) as afsAmount       

from    proc_cfa.dbo.P_AvailableForSale A

LEFT JOIN cte_PaymentStreams B ON SubString(A.afsSource,4,4)  =  B.DealID 

where   afsTransAction = @OrdTranID  

group by Left(afsSource,2), SubString(afsSource,4,4)

Open in new window

0
 
LVL 32

Expert Comment

by:Erick37
Comment Utility
Correction: CTE (common table expression)
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
don't do a left join, join on all rows, then select time matches

select ...From A,B Where YourTimeDifference(A.Time, B.Time) < 10

then you could use the UNION statement to add rows from first table that are not matched

or you could make the first select into a table variable, then select all rows from it Unioned with rows not counted in it




0
 

Author Comment

by:lrbrister
Comment Utility
Erick37:
How do I get the cp column from the top select?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 13

Assisted Solution

by:gamarrojgq
gamarrojgq earned 150 total points
Comment Utility
Try this, with this approach you can add the columns from the inner query to the main query but with an aggregate function since you main query have a Group clause
Select  Left(P_AvailableForSale.afsSource,2) as busType,  

        SubString(P_AvailableForSale.afsSource,4,4) as DealID,

		Sum(P_AvailableForSale.afsAMount) as afsAmount       

from    proc_cfa.dbo.P_AvailableForSale  

	LEFT JOIN (

		Select            

		        P_PaymentStreams.DealID  

		        ,CASE WHEN COUNT(P_PaymentStreams.PmtFrequency) > 1 THEN 'Split' ELSE MIN(P_PaymentStreams.PmtFrequency) END cp  

		        ,MAX(P_PaymentStreams.loanDate) LoanDate

		from    proc_lt.dbo.P_PaymentStreams 

		Group by P_PaymentStreams.DealID 

	) SecondTable ON SecondTable.DealId = SubString(P_AvailableForSale.afsSource,4,4)

where   P_AvailableForSale.afsTransAction = @OrdTranID  

group by Left(P_AvailableForSale.afsSource,2), SubString(P_AvailableForSale.afsSource,4,4)

Open in new window

0
 
LVL 32

Expert Comment

by:Erick37
Comment Utility
Like this:
;WITH cte_PaymentStreams (DealID,cp,LoanDate) AS

(

	Select            

			DealID  

			,CASE WHEN COUNT(PmtFrequency) > 1 THEN 'Split' ELSE MIN(PmtFrequency) END cp  

			,MAX(loanDate) LoanDate

	from    proc_lt.dbo.P_PaymentStreams 

	Group by DealID 

	order by DealID

)

Select  Left(afsSource,2) as busType,  

        SubString(afsSource,4,4) as DealID,

	Sum(afsAMount) as afsAmount,   

        B.cp,

        B.LoanDate,

        B.DealID    

from    proc_cfa.dbo.P_AvailableForSale A

LEFT JOIN cte_PaymentStreams B ON SubString(A.afsSource,4,4)  =  B.DealID 

where   afsTransAction = @OrdTranID  

group by Left(afsSource,2), SubString(afsSource,4,4)

Open in new window

0
 

Author Comment

by:lrbrister
Comment Utility
Erick37: and gamarrojgq:

Both answers worked fine...and valid applications.

I would like to award points to Erick37: with a nod to gamarrojgq with some points as well.

Anu problems with a 350/150 split?

I am also going to post an additional question based on this solution
0
 
LVL 13

Expert Comment

by:gamarrojgq
Comment Utility
Irbrister,

It sounds fine to me :)
0
 

Author Closing Comment

by:lrbrister
Comment Utility
Excellent guys.  Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now