linking the latest added record in an outer join

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

linking the latest added record in an outer join

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-16 : 06:49:50

I should build a left outer join to TABLE B, linking to each record from TABLE A the latest inserted record in TABLE B

Select *
FROM TABLE_A LEFT OUTER JOIN
TABLE_B ON TABLE_B.ITEMID = TABLE_A.ITEMID AND dbo.TABLE_B.DELIVERYDATE... is the latest date

how do I specify this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 06:57:39

[code]
Select *
FROM TABLE_A
LEFT OUTER JOIN
(SELECT b.*
FROM TABLE_B b
INNER JOIN (SELECT ITEMID,MAX(DELIVERYDATE) AS Latest
FROM TABLE_B
GROUP BY ITEMID
)b1
ON b1.ITEMID = b.ITEMID
AND b1.Latest = b.DELIVERYDATE
)tmp
ON tmp.ITEMID = TABLE_A.ITEMID
[/code]

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

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 07:00:11

Hi Try this query

Select TABLE_A.*
FROM TABLE_A
LEFT OUTER JOIN TABLE_B ON TABLE_B.ITEMID = TABLE_A.ITEMID
ORDER BY dbo.TABLE_B.DELIVERYDATE DESC

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 07:03:28

quote:
Originally posted by sql-programmers

Hi Try this query

Select TABLE_A.*
FROM TABLE_A
LEFT OUTER JOIN TABLE_B ON TABLE_B.ITEMID = TABLE_A.ITEMID
ORDER BY dbo.TABLE_B.DELIVERYDATE DESC

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

How will this give you latest record?
it will return you entire resultset including duplicate deliveries but it just orders them based on descending order of deliverydate

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

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 07:25:03

Yes visakh16, You are right. It displays all the records in the desc order instead of latest records only.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 09:27:58

quote:
Originally posted by sql-programmers

Yes visakh16, You are right. It displays all the records in the desc order instead of latest records only.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

I dont think that's what OP desires

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

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-16 : 09:41:07

Thank you... this is what I needed

Martin

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-16 : 09:45:49

Didn't see that there where more answers, because my boss was asking for some queries and I was going through two very stressfull hours. Thanks to Visakh!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 10:22:03

quote:
Originally posted by barnabeck

Thank you... this is what I needed

Martin

which one...mine or sql programmers suggestion?

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

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-16 : 10:46:23

yours, Visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 10:47:16

ok..so I guessed right
Anyways, you're welcome

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

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-17 : 10:50:32

Visakh, now that I delivered the figures to my boss and the stress from yesterday is gone, I am checking those queries again and just detected that somethings wrong.
Table A with its WHERE clause gives a total of 600 records, with all singular ITEMID values. So I suppose that the left outer link to TABLE B (giving exactly one match, specified by the latest deliverydate) would return exactly ONE record (or Null in case that there is no match). So the total of records should be exactly 600. But in fact there are 614 records found... ???

Do I have to repeat that where clause somewhere within your code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 10:54:11

quote:
Originally posted by barnabeck

Visakh, now that I delivered the figures to my boss and the stress from yesterday is gone, I am checking those queries again and just detected that somethings wrong.
Table A with its WHERE clause gives a total of 600 records, with all singular ITEMID values. So I suppose that the left outer link to TABLE B (giving exactly one match, specified by the latest deliverydate) would return exactly ONE record (or Null in case that there is no match). So the total of records should be exactly 600. But in fact there are 614 records found... ???

Do I have to repeat that where clause somewhere within your code?

sorry which is where condition you're referring to? If there was a where condition in your original query which didnt include in post definitely you've to include it.
I gave the suggestion based on your posted query and it didnt contain any where conditions

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

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-17 : 11:01:19

Sorry, I didn't include the where condition, because it just refers to TABLE A

Select *
FROM TABLE_A LEFT OUTER JOIN
TABLE_B ON TABLE_B.ITEMID = TABLE_A.ITEMID AND dbo.TABLE_B.DELIVERYDATE... is the latest date
WHERE TABLE_A.ITEMGROUP='043'

SO I did according to you:

Select *
FROM TABLE_A
LEFT OUTER JOIN
(SELECT b.*
FROM TABLE_B b
INNER JOIN (SELECT ITEMID,MAX(DELIVERYDATE) AS Latest
FROM TABLE_B
GROUP BY ITEMID
)b1
ON b1.ITEMID = b.ITEMID
AND b1.Latest = b.DELIVERYDATE
)tmp
ON tmp.ITEMID = TABLE_A.ITEMID

WHERE TABLE_A.ITEMGROUP='043'

Does this change anything? Should be the same to me!
Thank you for your prompt assistence

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 11:05:18

yep...that looks fine now

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

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-17 : 11:10:13

Well, if this looks fine, why doesn't this give a 1:1 match and returns more than:

Select *
FROM TABLE_A
WHERE TABLE_A.ITEMGROUP='043'

=> 600 records

Select *
FROM TABLE_A
WHERE TABLE_A.ITEMGROUP='043'
GROUP BY ITEMID

=>600 Records

while the complete Query with your code gives 614

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 11:20:30

are you sure there are no multiple records existing in TABLE_B with same max delivery date? what does below return?


SELECT b.ITEMID,b.DELIVERYDATE
FROM TABLE_B b
INNER JOIN (SELECT ITEMID,MAX(DELIVERYDATE) AS Latest
FROM TABLE_B
GROUP BY ITEMID
)b1
ON b1.ITEMID = b.ITEMID
AND b1.Latest = b.DELIVERYDATE
GROUP BY b.ITEMID,b.DELIVERYDATE
HAVING COUNT(*) > 1

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

barnabeck
Posting Yak Master

236 Posts

Posted - 2011-12-17 : 11:31:43

You've got! I haven't tried this out cause it's allways a certain effort to adapt the code to my tables...
but I looked at the comumn DELIVERYDATE: being of type datetime I nevery could imagine doubles, but as the time part is cropped to 0:00:00 there ARE actually doubles!!
Thank you, you did it again!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 12:03:19

quote:
Originally posted by barnabeck

You've got! I haven't tried this out cause it's allways a certain effort to adapt the code to my tables...
but I looked at the comumn DELIVERYDATE: being of type datetime I nevery could imagine doubles, but as the time part is cropped to 0:00:00 there ARE actually doubles!!
Thank you, you did it again!!

no problem. you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources