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
30250 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.05 seconds. Powered By: Snitz Forums 2000