Join Challenge - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join Challenge

Author

Topic

sqlgurufrank
Starting Member

2 Posts

Posted - 2006-02-04 : 05:45:06

Have tried a lot, nothing worked! Need help desperately.
I need the sql syntax for the following:

Tables:

Customers

CustomerID : CustomerName
1 : Disney
2 : Paramount

Products

ProductID : ProductName
1 : sparkle
2 : raindrop
3 : MR16

Orders

OrderID : OrderDate : CustomerID
1 : 2005-10-12 : 2
2 : 2006-01-03 : 1

OrderDetails

OrderDetailID : OrderID : ProductID : Quantity
1 : 1 : 3 : 10
2 : 1 : 2 : 5
3 : 2 : 1 : 3
4 : 2 : 3 : 20

DeliveryDetails

DeliveryDetailID : OrderDetailID : Quantity
1 : 1 : 2
2 : 2 : 5
3 : 1 : 2

Now I would like to see the backorders

Something like:

OrderDetailID : CustomerName : ProductID : ProductName : QuantityOrdered: Quantity Delivered : BackOrder
1 : Paramount : 3 : MR16 : 10 : 4 (group deliveries of orderdetail 1) : 6 (ordered-delivered)
4 : Disney : MR16 : 20 : 0 : 20

I hope somebody can help me with this one.

Sprinjee
Starting Member

42 Posts

Posted - 2006-02-04 : 06:47:57

Something like this?:

select od.OrderDetailID , cs.CustomerName, pr.ProductID , pr.ProductName, od.quantity, dl.quantity, (od.quantity - dl.quantity) as BackOrder from OrderDetails od
inner join Orders Or on od.OrderID = Or.OrderID
inner join Customers Cs on Or.CustomerID = Cs.Customer.id
inner join Products pr on pr.Productid = od.productid
inner join DeliveryDetails dl on od.OrderDetailID = dl.OrderDetailID

sqlgurufrank
Starting Member

2 Posts

Posted - 2006-02-04 : 07:47:31

Thanks for your help. We are closer to the solution now but still one challenge to go. Your solution does not calculate the backorder so I adapted to the following:

select od.OrderDetailID , cs.CustomerName, pd.ProductID , pd.ProductName, od.quantity AS Ordered, SUM(dl.quantity) AS Delivered, (od.quantity - SUM(dl.quantity)) as BackOrder from od
inner join ord on od.OrderID = ord.OrderID
inner join cs on ord.CustomerID = cs.CustomerID
inner join pd on pd.Productid = od.productid
inner join dl on od.OrderDetailID = dl.OrderDetailID
GROUP BY dl.OrderDetailID

Those od.OrderDetailID backorders for which it can find deliveries. However I would also like to display in the same query those od.OrderDetailIDs for which it cannot find deliveries.

Any Ideas?

quote:
Originally posted by Sprinjee

Something like this?:

select od.OrderDetailID , cs.CustomerName, pr.ProductID , pr.ProductName, od.quantity, dl.quantity, (od.quantity - dl.quantity) as BackOrder from OrderDetails od
inner join Orders Or on od.OrderID = Or.OrderID
inner join Customers Cs on Or.CustomerID = Cs.Customer.id
inner join Products pr on pr.Productid = od.productid
inner join DeliveryDetails dl on od.OrderDetailID = dl.OrderDetailID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-04 : 10:00:10

quote:
Those od.OrderDetailID backorders for which it can find deliveries. However I would also like to display in the same query those od.OrderDetailIDs for which it cannot find deliveries.

Use LEFT JOIN

select	od.OrderDetailID, c.CustomerName, od.ProductID, p.ProductName, sum(od.Quantity) as QuantityOrdered, sum(isnull(dd.Quantity, 0)) as QuantityDelivered, sum(od.Quantity - isnull(dd.Quantity, 0)) as BackOrder
from	Orders o
	inner join OrderDetails od 
	on 	o.OrderID	= od.OrderID
	inner join Customers c
	on	o.CustomerID	= c.CustomerID
	inner join Products p
	on	od.ProductID	= p.ProductID
	left join DeliveryDetails dd
	on	od.OrderDetailID	= dd.OrderDetailID
where	od.Quantity - isnull(dd.Quantity, 0) <> 0
group by od.OrderDetailID, c.CustomerName, od.ProductID, p.ProductName
order by od.OrderDetailID

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources