Query to pull multiple reco assigned to a customer

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

Transact-SQL (2005)

Query to pull multiple reco assigned to a customer

Author

Topic

lisakrebs
Starting Member

10 Posts

Posted - 2010-10-19 : 13:26:47

I have 3 tables (Customer, Cust_Relation, and Sales_Rep). I need to to find all the customers that have more than 1 sales reps assigned. I need to use the Cust_Relation table because its the only way to link Customer and Sales_Rep.

Here is a very small sampling of data for each table along with my expected results.

Customer:
Customer ID Customer Name
1 John Doe
2 John Smith
3 Jane Doe

Cust_Relation:
Customer ID Sales Rep ID
1 1
1 2
2 1
3 2
3 4

Sales_Rep:
Sales Rep ID Rep Name
1 House Account
2 Bryan Brown
3 Aaron Holtz
4 Eric Zavilla
5 Chip Hurt

Result Set:
Customer ID Customer Name Rep Name
1 John Doe House Account
1 John Doe Bryan Brown
3 Jane Doe Bryan Brown
3 Jane Doe Eric Zavilla

Query:
select c.customerid, c.customername, s.repname
from customer c
where 1 > (select count(*) from salesrep sr inner join cust_relation cr on cr.repid = sr.repid
inner join customer c on c.customerid = cr.customerid)

Thanks for any and all feedback!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:35:31

[code]
SELECT c.customerid, c.customername
FROM Customer c
INNER JOIN (SELECT Customer_ID,COUNT(Sales_Rep_ID) AS RepCnt
FROM Cust_Relation
GROUP BY Customer_ID)c1
On c1.Customer_ID = c.Customer_ID
WHERE c1.repCnt >1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lisakrebs
Starting Member

10 Posts

Posted - 2010-10-19 : 15:04:06

That worked perfectly, thanks.

Have one more question since I need to pass this to the user community to review I have to add the sales rep name. When I do that I loose records (approximately 300) from the original query what am I doing wrong (highlighted in red is what I added):

select c.customerid, c.name1, c2.salesrep
from customer c
inner join (select customerid, salesrepid, count(caesaruserid) as repcnt
from cust_relation
group by customerid, salesrepid) c1
on c1.customerid = c.customerid
left outer join sales_rep c2 on c1.salesrepid = c2.salesrepid
where c1.repcnt > 1

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 15:08:16

in that case better to do like this


SELECT customerid,name1,salesrep
FROM
(
SELECT c.customerid, c.name1, sr.salesrep,
COUNT(sr.salesrep) OVER (PARTITION BY c.customerid) AS SRepCnt
FROM Customer c
INNER JOIN cust_relation cr
ON cr.customerid = c.customerid
INNER JOIN sales_rep sr
on sr.salesrepid = cr.salesrepid
)t
WHERE SRepCnt > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lisakrebs
Starting Member

10 Posts

Posted - 2010-10-19 : 15:28:57

Perfect, thanks for all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:52:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources