JOIN problem - 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 problem

Author

Topic

dursaliye
Starting Member

22 Posts

Posted - 2005-05-23 : 18:11:30

Hello,
I've a query using JOINS but gives wrong result in the column "forobservingord".
I've another query that uses function and it gives the right result. I want to know how can I solve this problem with the first query without using function?

----------------------------------------------
CREATE TABLE [order] (
[id] [int] NULL ,
[date_order] [datetime] NULL ,
[product] [char] (10) ,
[quantity] [int] NULL
)
go
CREATE TABLE [distribute] (
[id] [int] NULL ,
[date_distribute] [datetime] NULL ,
[product] [char] (10),
[quantity] [int] NULL
)
SET dateformat ymd
INSERT INTO distribute (id,date_distribute,product,quantity) VALUES
(51,'2005-01-01','aaa',10)
INSERT INTO distribute (id,date_distribute,product,quantity) VALUES
(52,'2005-01-04','aaa',13)
INSERT INTO distribute (id,date_distribute,product,quantity) VALUES
(53,'2005-01-05','aaa',3)
INSERT INTO distribute (id,date_distribute,product,quantity) VALUES
(54,'2005-01-06','aaa',-2)
INSERT INTO distribute (id,date_distribute,product,quantity) VALUES
(55,'2005-01-07','aaa',8)
INSERT INTO distribute (id,date_distribute,product,quantity) VALUES
(56,'2005-01-08','aaa',45)
INSERT INTO distribute (id,date_distribute,product,quantity) VALUES
(57,'2005-01-10','aaa',10)
INSERT INTO [order] (id,date_order,product,quantity) VALUES
(11,'2005-01-01','aaa',10)
INSERT INTO [order] (id,date_order,product,quantity) VALUES
(12,'2005-01-03','aaa',20)
INSERT INTO [order] (id,date_order,product,quantity) VALUES
(13,'2005-01-05','aaa',30)
INSERT INTO [order] (id,date_order,product,quantity) VALUES
(14,'2005-01-08','aaa',15)
INSERT INTO [order] (id,date_order,product,quantity) VALUES
(15,'2005-01-09','aaa',10)
----------------------------------------------
First query:
SELECT o1.id,o1.date_order,o1.quantity,
ISNULL(SUM(d2.quantity),0)AS forobservingdist,
ISNULL(SUM(o2.quantity),0)AS robservingord
FROM [order] o1
LEFT JOIN [order] o2 ON o1.id>=o2.id and o1.product=o2.product
LEFT JOIN Distribute d1 ON o1.date_order<=d1.date_distribute and
o1.product=d1.product
LEFT JOIN Distribute d2 ON d2.id<=d1.id and d2.product=d1.product
GROUP BY o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product
ORDER BY o1.date_order
----------------------------------------------
Second query:
CREATE FUNCTION getorderdogan
(@id int,@product nvarchar(50))
RETURNS int
AS
BEGIN

DECLARE @SUM AS int
SELECT @SUM = SUM(o2.quantity) FROM [order] o2 WHERE o2.id<=@id and
o2.product=@product
RETURN @SUM
END

GO
CREATE FUNCTION getdistributedogan
(@id int,@product nvarchar(50))
RETURNS int
AS
BEGIN

DECLARE @SUM AS int
SELECT @SUM = SUM(d2.quantity) FROM Distribute d2 WHERE d2.id<=@id and
d2.product=@product
RETURN ISNULL(@SUM,0)
END

SELECT o1.id,o1.date_order,o1.product, o1.quantity,d1.id AS Distribute_id ,
d1.date_distribute,
dbo.getdistributedogan(d1.id,d1.product)AS forobservingdist,
dbo.getorderdogan(o1.id,o1.product)AS forobservingord
FROM dbo.[order] o1
LEFT OUTER JOIN Distribute d1 ON o1.date_order<=d1.date_distribute and
o1.product=d1.product
GROUP BY o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-23 : 23:48:17

hmmm

"Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it. "

"The questions you ask consistently will create either enervation or enjoyment, indignation or inspiration, misery or magic. Ask the questions that will uplift your spirit and push you along the path of human excellence. "

K.I.S.S - Keep it simple stupid

raclede™

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-24 : 01:20:13

raclede, that was possibly your most useless post yet. I don't know if you are just trying to increase your postcount, but you aren't being helpful.

dursaliye, I would consider using a correlated subquery for this.


SELECT 
	o1.id,o1.date_order,o1.quantity, o1.product, o1.quantity,d1.id AS Distribute_id,
	( SELECT SUM(quantity) FROM [order] o2 WHERE o1.id >= o2.id AND o1.product = o2.product ) as forobservingord,
	( SELECT SUM(quantity) FROM distribute d2 WHERE d2.id <= d1.id and d2.product=d1.product ) as forobservingdist
FROM 
	[order] o1 
LEFT JOIN Distribute d1 ON o1.date_order <= d1.date_distribute and o1.product=d1.product 
ORDER BY o1.date_order

That gives you the required result. I couldn't figure out exactly why you wanted it to look like that though, there are possibly better ways to get useful information out, but I don't understand your requirements.

Damian
Ita erat quando hic adveni.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-24 : 01:27:49

Optionally, you could use derived tables. Hundred different ways to skin a cat kind of thing:


-- --Second query: 
-- CREATE FUNCTION getorderdogan 
-- (@id int,@product nvarchar(50)) 
-- RETURNS int 
-- AS 
-- BEGIN 
-- 
-- DECLARE @SUM AS int 
-- SELECT @SUM = SUM(o2.quantity) FROM [order] o2 WHERE o2.id<=@id and 
-- o2.product=@product 
-- RETURN @SUM 
-- END 
-- 
-- GO 
-- CREATE FUNCTION getdistributedogan 
-- (@id int,@product nvarchar(50)) 
-- RETURNS int 
-- AS 
-- BEGIN 
-- 
-- DECLARE @SUM AS int 
-- SELECT @SUM = SUM(d2.quantity) FROM Distribute d2 WHERE d2.id<=@id and 
-- d2.product=@product 
-- RETURN ISNULL(@SUM,0) 
-- END 



--First query: 

SELECT 
	o1.id,
	o1.date_order,
	o1.product, 
	o1.quantity,
	d1.id AS Distribute_id, 
	d1.date_distribute, 
	SUM(d2.quantity) AS forobservingdist, 
	SUM(o2.quantity) AS forobservingord 
FROM 
	dbo.[order] o1 
	LEFT OUTER JOIN Distribute d1 ON o1.date_order<=d1.date_distribute 
		AND o1.product=d1.product 
	LEFT OUTER JOIN (
		SELECT SUM(quantity) AS quantity, id, product
		FROM dbo.[order]
		GROUP BY id, product) o2 ON o2.id <= o1.id 
		AND o2.product = o1.product
	LEFT OUTER JOIN (
		SELECT ISNULL(SUM(quantity),0) AS quantity, id, product
		FROM Distribute
		GROUP BY id, product) d2 ON d2.id <= d1.id
		AND d2.product = d1.product
GROUP BY 
	o1.id, 
	o1.date_order,
	o1.product, 
	o1.quantity,
	d1.id,
	d1.date_distribute,
	d1.quantity,
	d1.product



SELECT 
	o1.id,
	o1.date_order,
	o1.product, 
	o1.quantity,
	d1.id AS Distribute_id, 
	d1.date_distribute, 
	dbo.getdistributedogan(d1.id,d1.product)AS forobservingdist, 
	dbo.getorderdogan(o1.id,o1.product)AS forobservingord 
FROM 
	dbo.[order] o1 
	LEFT OUTER JOIN Distribute d1 ON o1.date_order<=d1.date_distribute 
	AND o1.product=d1.product 
GROUP BY 
	o1.id, 
	o1.date_order,
	o1.product, 
	o1.quantity,
	d1.id,
	d1.date_distribute,
	d1.quantity,
	d1.product 


SELECT 
	o1.id,o1.date_order,o1.quantity, o1.product, o1.quantity,d1.id AS Distribute_id,
	( SELECT SUM(quantity) FROM [order] o2 WHERE o1.id >= o2.id AND o1.product = o2.product ) as forobservingord,
	( SELECT SUM(quantity) FROM distribute d2 WHERE d2.id <= d1.id and d2.product=d1.product ) as forobservingdist
FROM 
	[order] o1 
LEFT JOIN Distribute d1 ON o1.date_order <= d1.date_distribute and o1.product=d1.product 
ORDER BY o1.date_order

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dursaliye
Starting Member

22 Posts

Posted - 2005-05-24 : 13:39:08

Thanks, Merkin. It's reporting orders and distributions till a date even there is no entry at that date.

raclede -> You really don't help. You only cause us to waste time to read your relpy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources