Show salesperson if no Sales - 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

Show salesperson if no Sales

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-11-13 : 14:41:19

I have a salesman file and a sales history file.

I want to list all Salesman in the Salesman file along with there sales. If there is no record in the sales file I still want to see the salesman listed.

I've tried this but it doesn't seem to work. The code below is only listing salesman if they have sales in period 10.


select s.humres_id , sum(q.Sales) as Sales, sum(q.Cost)as cost
from arslmfil_SQL s left outer join QIVSalesMTDYTDCustSalesPerson Q on s.humres_id = q.slspsn_no
where Year=2014 and Period = 10
group by s.humres_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-13 : 14:50:19

Try add the WHERE condition to the ON of the JOIN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-11-13 : 14:51:00

Can you elaborate on that? not sure what you mean?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-13 : 14:57:57

select s.humres_id , sum(q.Sales) as Sales, sum(q.Cost)as cost
from arslmfil_SQL s left outer join QIVSalesMTDYTDCustSalesPerson Q on s.humres_id = q.slspsn_no and Year=2014 and Period=10
group by s.humres_id

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-11-13 : 14:58:53

That did the Trick thanks a lot.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 15:00:25

The WHERE condition is applied as a filter on the result of the JOIN. So, it can (does, in your case) reduce the result set. If you put the conditions in the ON clause, since its and OUTER join, it preserves the LEFT side of the join in the result set.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-13 : 15:32:02

Here's an article on the topic: http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-13 : 15:32:41

quote:
Originally posted by Vack

That did the Trick thanks a lot.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources