joining 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

General SQL Server Forums

New to SQL Server Programming

joining problem

Author

Topic

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-10-07 : 03:02:35

hi

i have 3 table. branches,sales,purchase
i have to get the sum(sales),sum(purchse) for each branch. this is my query. but i am not getting the proper result. i think its the problem with join. please help me out

select bm.branch_name,sum(am.total) sales,sum(pm.total)purchase from
branch bm,sales am,purchases pm where bm.branch_code=am.branch_code
and bm.branch_code=pm.branch_code group by bm.branch_name

thanks

suji

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-10-07 : 03:18:15

give me some ref. of website/tutorials for studying sql(intermediate).
specially for subqueries.

thanks

suji

Kristen
Test

22859 Posts

Posted - 2006-10-07 : 03:42:50

That will join, for each branch, every sales record to every purchase record - a Cartesian join - so I expect you are getting some very big numbers as a result!

Try this:


SELECT	bm.branch_name,
	am.sales,
	pm.purchase 
FROM	branch AS bm
	LEFT OUTER JOIN
	(
		SELECT	am.branch_code,
			sum(am.total) AS sales
		FROM	sales AS am
		GROUP BY am.branch_code
	) am
		ON bm.branch_code = am.branch_code
	LEFT OUTER JOIN
	(
		SELECT	pm.branch_code,
			sum(pm.total) AS purchase
		FROM	sales AS pm
		GROUP BY pm.branch_code
	) pm
		ON bm.branch_code = pm.branch_code
ORDER BY bm.branch_name

Kristen

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-10-07 : 04:21:21

thanks
ur right. i am getting some big numbers and its slow too.

suji

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources