Need help with JOIN - 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

Need help with JOIN

Author

Topic

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-05-22 : 17:46:27

On previous join, I use Row_number() so now my temp table look like below:

Account, Date, Ranking
100 2/1/2011 1
100 2/15/2011 2
100 2/25/2011 3
100 3/10/2011 4
200 5/1/2012 1
200 5/22/2012 2

I need the join the data from the temp table to the table1 that has data like below:

Loan, Ship_date, Method
100 2/9/2011 USPS
100 2/19/2011 FedEx
100 2/28/2011 FedEx
100 3/16/2011 USPS
200 5/15/2012 USPS

I want to join the two tables together where I want to display
Account, Date, Ship_date, Method. I need to join the two tables where Account = Loan AND Ship_date >= Date.

I want the result to be like below, but I can't get it to work.

Account, Date, Ship_date, Method
100 2/1/2011 2/9/2011 USPS
100 2/15/2011 2/19/2011 FedEx
100 2/25/2011 2/28/2011 FedEx
100 3/10/2011 3/16/2011 USPS
200 5/1/2012 5/15/2012 USPS

Please help. Your help is greatly appreciated. Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2012-05-22 : 18:49:11

I Fault M$ for this

The data is meaningless

Another overused, misunderstood.... enhancement

How did you get your "Ranking"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-23 : 03:03:56

What you are trying to do doesn't go by the logic you are telling us.

You are actually trying to join the first row of first table with the first row in second table. For that you can use Row_Number() Over (Order By) as follows:


Select a.Account, a.Date, b.Ship_date, b.Method From
(Select *, ROW_NUMBER() Over (Order By Date) As rn From Ex) As a
JOIN
(Select *, ROW_NUMBER() Over (Order By Ship_Date) As rn From Ex1) As b
ON a.rn = b.rn

If you still want to add the check for account = loan and Ship_Date >= Date the you can add the following where clause to the above mentioned code:


Where a.Account = b.Loan AND b.Ship_Date >= a.Date

Hope this helps.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:40:43

quote:
Originally posted by siumui

On previous join, I use Row_number() so now my temp table look like below:

Account, Date, Ranking
100 2/1/2011 1
100 2/15/2011 2
100 2/25/2011 3
100 3/10/2011 4
200 5/1/2012 1
200 5/22/2012 2

I need the join the data from the temp table to the table1 that has data like below:

Loan, Ship_date, Method
100 2/9/2011 USPS
100 2/19/2011 FedEx
100 2/28/2011 FedEx
100 3/16/2011 USPS
200 5/15/2012 USPS

I want to join the two tables together where I want to display
Account, Date, Ship_date, Method. I need to join the two tables where Account = Loan AND Ship_date >= Date.

I want the result to be like below, but I can't get it to work.

Account, Date, Ship_date, Method
100 2/1/2011 2/9/2011 USPS
100 2/15/2011 2/19/2011 FedEx
100 2/25/2011 2/28/2011 FedEx
100 3/10/2011 3/16/2011 USPS
200 5/1/2012 5/15/2012 USPS

Please help. Your help is greatly appreciated. Thank you


SELECT t.Account, t.Date, t1.Ship_date, t1.Method
FROM temptable t
CROSS APPLY (SELECT TOP 1 *
             FROM table1
             WHERE Loan = t.Account
             AND Ship_Date > t.Date
             ORDER BY Ship_date
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources