full outer join with Where clause

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 2008 Forums

Transact-SQL (2008)

full outer join with Where clause

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2013-10-05 : 03:52:59

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

30421 Posts

Posted - 2013-10-05 : 08:47:59

[code]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[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources