Over the next few days we are going to start the migration to a new forum application. I'm going to start with high post count and active users. You may receive an invitation to the new forums. It's not spam. It's just me trying to seed the user base. My goal is to open it up over the weekend.

Once we open the site we'll block registrations here. We should be open shortly on the new site.

SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 full outer join with Where clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thanksfor help
Posting Yak Master

106 Posts

Posted - 10/05/2013 :  03:52:59  Show Profile  Reply with Quote
Hi,

I have 2 table depart and wk_sales need to full outer join to get all the depart even when there is no sales for that week.

Wk_sales
wk_1-----dept1------------1000
wk_1-----dept2------------2000
wk_1-----dept3------------3000
wk_2-----dept1------------2000
wk_2-----dept4------------1000
wk_3-----dept2------------2000
wk_3-----dept3------------2000
wk_3-----dept4------------1000

dept
d1------dept1
d2------dept2
d3------dept3
d4------dept4
d5------dept5

Query result

wk_1-----dept1------------1000
wk_1-----dept2------------2000
wk_1-----dept3------------3000
wk_1-----dept4------------0
wk_1-----dept5------------0
wk_2-----dept1------------2000
wk_2-----dept2------------0
wk_2-----dept3------------0
wk_2-----dept4------------1000
wk_2-----dept5------------0
Wk3----------------------------



Select * from WK_sales s
full outer join dept d on s.dept_code = d.dept_code

only gives extra row for dept5 in the end.
But I need every dept for each week.

any suggestion will be helpfull

thanks

SwePeso
Patron Saint of Lost Yaks

Sweden
30419 Posts

Posted - 10/05/2013 :  08:47:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		d.Dept,
		w.Wk,
		COALESCE(q.Sales, 0) AS Sales
FROM		dbo.Dept AS d
CROSS JOIN	(
			SELECT		Wk
			FROM		dbo.Wk_Sales
			GROUP BY	Wk
		) AS w
LEFT JOIN	dbo.Wk_Sales AS q ON q.Dept = d.Dept
			AND q.Wk = w.Wk



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000