Single result for multiple matches from 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

SQL Server 2008 Forums

Transact-SQL (2008)

Single result for multiple matches from Join

Author

Topic

ryniers
Starting Member

5 Posts

Posted - 2011-01-25 : 04:22:29

Hi guys

I need some help

I have a product table and a orders table
I have multiple orders for a product but I only need the earliest order to show.

Products table
ID Branch BranchCode Productcode ProductName
1 Alpha za 123 Bread
2 Beta xa 321 Milk

Orders table
ID BranchCode ProductCode DelDate
1 za 123 10 Aug
2 za 123 15 Aug
3 za 321 9 Aug
4 za 321 11 Aug
5 xa 123 11 Aug
6 xa 123 16 Aug
7 xa 321 10 Aug
8 xa 321 12 Aug

I will join on branch code and Product code, what I need to get is this:

Branch BranchCode ProductCode ProductName DelDate
Alpha za 123 Bread 10Aug
Alpha za 321 Milk 9Aug
Beta xa 123 Bread 11Aug
Beta xa 321 Milk 10Aug

How will I achieve this?

Thanks for your help

Rynier

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 04:39:18

SELECT p.Branch, p.BranchCode, p.ProductCode, p.ProductName, o.DelDate
FROM Products AS p
INNER JOIN (
SELECT BranchCode, ProductCode, MIN(DelDate) AS DelDate
FROM Orders
GROUP BY BranchCode, ProductCode
) AS o ON o.BranchCode = p.BranchCode
AND o.ProductCode = p.ProductCode

N 56°04'39.26"
E 12°55'05.63"

ryniers
Starting Member

5 Posts

Posted - 2011-01-25 : 04:50:16

Thanks for the quick reply. Will give it a go.

ryniers
Starting Member

5 Posts

Posted - 2011-01-25 : 05:49:19

Hi Peso

That works, thank you. Just one thing

AS o ON o.BranchCode = o.BranchCode
AND o.ProductCode = o.ProductCode

Should be

AS o ON p.BranchCode = o.BranchCode
AND p.ProductCode = o.ProductCode

Confused me there for a moment.

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 06:05:03

Yes. I changed my code above.

N 56°04'39.26"
E 12°55'05.63"

ryniers
Starting Member

5 Posts

Posted - 2011-01-25 : 06:59:33

Hi Pesco

Although your solution works in the example I gave you, it doesn't seem to work when I apply it my actual table. I must be missing something.
Let me show you. Here I have 4 outstanding orders for a branch of which I only need the earliest one:
This is my query: ( I am not selecting all the fields but the ones I ommitted are identical in this result anyway)

SELECT [SupplierOrderID]
,[DimTimeFinancialID]
,[PurchaseOrderNumber]
,[DeliveryDate]
,[NoLaterThanDate]
,[OrderQty]
,[DeliveryDateCalendar] As DeliveryDate2
FROM FactSupplierOrders
WHERE DimProductHierarchyID = '1200388'
AND DimCompanyHierarchyID = '907'
AND OrderStatusID < '3'

This is the result:
SupplierOrderID DimTimeFinancialID PurchaseOrderNumber DeliveryDate NoLaterThanDate OrderQty DeliveryDate2
151218973 2157 55721100425503 2267 2281 10 2011-03-30
151224124 2157 55721000425503 2224 2238 10 2011-02-15
151236592 2211 55757300425503 2328 2342 7 2011-05-30
151276484 2211 55757200425503 2298 2312 17 2011-04-30

Then I run This Query:

SELECT [SupplierOrderID]
,[DimTimeFinancialID]
,[PurchaseOrderNumber]
,[DeliveryDate]
,[NoLaterThanDate]
,[OrderQty]
,MIN([DeliveryDateCalendar]) As DeliveryDate2
FROM FactSupplierOrders
WHERE DimProductHierarchyID = '1200388'
AND DimCompanyHierarchyID = '907'
AND OrderStatusID < '3'
GROUP BY [SupplierOrderID]
,[DimTimeFinancialID]
,[PurchaseOrderNumber]
,[DeliveryDate]
,[NoLaterThanDate]
,[OrderQty]

And this is the result:

SupplierOrderID DimTimeFinancialID PurchaseOrderNumber DeliveryDate NoLaterThanDate OrderQty DeliveryDate2
151218973 2157 55721100425503 2267 2281 10 2011-03-30
151224124 2157 55721000425503 2224 2238 10 2011-02-15
151236592 2211 55757300425503 2328 2342 7 2011-05-30
151276484 2211 55757200425503 2298 2312 17 2011-04-30

I should only be getting back one line with deliveryDate 2011-02-15
What am I missing?
Thanks
Rynier

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 11:08:45

quote:
Originally posted by ryniers

Hi Pesco

Although your solution works in the example I gave you, it doesn't seem to work when I apply it my actual table. I must be missing something.

Common sense? Just kidding...
If you want a solution for your actual problem, why post a pseudo-problem?
Keep it simple. Post your actual problem and you'll get an actual solution. Simple as that.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 11:12:29

[code];WITH cteSource
AS (
SELECT SupplierOrderID,
DimTimeFinancialID,
PurchaseOrderNumber,
DeliveryDate,
NoLaterThanDate,
OrderQty,
DeliveryDateCalendar,
ROW_NUMBER() OVER (PARTITION BY PurchaseOrderNumber ORDER BY DeliveryDateCalendar) AS RecID
FROM dbo.FactSupplierOrders
WHERE DimProductHierarchyID = '1200388'
AND DimCompanyHierarchyID = '907'
AND OrderStatusID < '3'
)
SELECT SupplierOrderID,
DimTimeFinancialID,
PurchaseOrderNumber,
DeliveryDate,
NoLaterThanDate,
OrderQty,
DeliveryDateCalendar
FROM cteSource
WHERE RecID = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-01-25 : 13:14:04

This is the first time in history that the Peso Mind Reading Act™ has failed!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 14:34:45

Am too busy.

Writing a new presenation about Relational Division and writing on my new book.
Pregnant wife (expecting on March 19-21), and inducting my youngest to kindergarten.

N 56°04'39.26"
E 12°55'05.63"

ryniers
Starting Member

5 Posts

Posted - 2011-01-26 : 04:20:09

Peso Thanks for your help.

Got it working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources