Simple query to find certain customers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Simple query to find certain customers?

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2005-10-17 : 11:29:06

In our business, each customer is assigned an administrator. Sometimes however, depending on how time consuming that customer is, 2 administrators are assigned to a single customer. If there are two or more administrators however, one of them needs to be set as the primary admin. If there's only 1 admin for a particular customer, they should always be marked as the primary admin.

Soooo, now I need to perform a query that returns all customers who have an administrator but nobody ever set any of their admins as a primary admin.

There's basically 2 tables I need to work with to accomplish this:
TCustomers
TCustomerContacts

These two tables are joined by TCustomers.custid = TCustomerContacts.custContCustId

You can tell whether or not an admin is primary or not by looking at the field custContPrimaryAdminInd in TCustomerContacts. It has either a 'Y' or 'N' value.

So as an example, my first customer may have only 1 primary admin. So in the TCustomerContacts table, he would have 1 row with custContPrimaryAdminInd = 'Y'.

My second customer however, may have 2 admins but neither one is set as the primary admin. He would have 2 rows in TCustomerContacts with the custContPrimaryAdminInd field in both rows set to 'N'. These are the customers I wish to return in my query. The ones who have no primary admin set.

How could I do a query like this?

Thanks in advance.

-Goalie35

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-17 : 12:38:26

select custContCustId
from TCustomerContacts
where custContPrimaryAdminInd = 'N'
group by custContCustId
having count(*)>1

Go with the flow & have fun! Else fight the flow

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2005-10-17 : 13:21:02

Hi Spirit1. Thanks for the help so far but that one didn't quite work. What I'm really trying to get are just the customers who have no primary admin set.

I modified your query a little because I want to see the customer name but had no luck retrieving the propper rows:

select custContCustId, custName, custContPrimaryAdminInd
from TCustomerContacts
inner join TCustomers on custId = custContPrimaryAdminInd
where custContPrimaryAdminInd = 'N'
group by custContCustId, custName,custContPrimaryAdminInd
having count(*)>1

Thanks again.

-Goalie35

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-17 : 13:30:36

you need to give us more info:
follow the steps here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

seems to me you don't know how group by works. you might want to look into that.
try:
select t.custContCustId, custName, t.custContPrimaryAdminInd
from (select custContCustId, custContPrimaryAdminInd from TCustomerContacts where custContPrimaryAdminInd = 'N'
group by custContCustId, custContPrimaryAdminInd having count(*)>1) t
join TCustomers on custId = t.custContPrimaryAdminInd

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources