CODEWITHSUNDEEP
sqlsql-server
MrPeanutbutter
MrPeanutbutter

Reputation: 83

Select rows with same value in one column but different value in another column

I've been trying to build this query but am new to SQL so I'd really appreciate some help.

In the below table example, I have a Customer Code, a linked Customer Code (which is used to link a child customer to a parent customer), a salesperson, and other irrelevant columns. The goal is to have one Salesperson for each parent customer and it's children. So in the example, CustCode #100 is the parent of itself, #200, #500, and #800. All of these accounts have the same Salesperson (JASON) which is perfect. But for CustCode #300, it is the parent of itself, #400, and #600. However, there isn't one salesperson assigned - its both JIM and SUZY. I want to build a query that shows all accounts for this example. Basically, accounts where the Salesperson field isn't the same value for all of it's child customers.

I tried a Where clause for Salesperson <> Salesperson but its not showing up right. 

+-----------+-----------------+------------+----------------------+
| CustCode  | Linked CustCode | Salesperson| additional columns...|
+-----------+-----------------+------------+----------------------+
| 100       |       100       |    JASON   |          ...         |
| 200       |       100       |    JASON   |          ...         |
| 300       |       300       |    JIM     |          ...         |
| 400       |       300       |    JIM     |          ...         |
| 500       |       100       |    JASON   |          ...         |
| 600       |       300       |    SUZY    |          ...         |
| 700       |       NULL      |    JIM     |          ...         |
| 800       |       100       |    JASON   |          ...         |
+-----------+-----------------+------------+----------------------+

Thanks so much for your help!

Upvotes: 3

Views: 11978

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

This solution uses a recursive CTE first to build the hierarchy and find the leading code for each row, even if a linked code points to a row which is pointing to an upper row itself.

The final query shows the count of different Salespersons:

DECLARE @tbl TABLE(CustCode INT,[Linked CustCode] INT,Salesperson VARCHAR(100));
INSERT INTO @tbl VALUES
 (100,100,'JASON')
,(200,100,'JASON')
,(300,300,'JIM')
,(400,300,'JIM')
,(500,100,'JASON')
,(600,300,'SUZY')
,(700,NULL,'JIM')
,(800,100,'JASON');

--The query

WITH CleanUp AS
(
    SELECT CustCode 
          ,CASE WHEN [Linked CustCode]=CustCode THEN NULL ELSE [Linked CustCode] END AS [Linked CustCode]
          ,Salesperson
    FROM @tbl
)
,recCTE AS
(
    SELECT CustCode AS LeadingCode,CustCode,[Linked CustCode],Salesperson
    FROM CleanUp
    WHERE [Linked CustCode] IS NULL

    UNION ALL

    SELECT recCTE.LeadingCode,t.CustCode,t.[Linked CustCode],t.Salesperson
    FROM recCTE
    INNER JOIN CleanUp AS t ON t.[Linked CustCode]=recCTE.CustCode
)
SELECT LeadingCode,COUNT(DISTINCT Salesperson) AS CountSalesperson
FROM recCTE
GROUP BY LeadingCode

The result

LeadingCode CountSalesperson
100         1
300         2
700         1

Upvotes: 1

Nagaraj Raveendran
Nagaraj Raveendran

Reputation: 1235

You can do self join on the table.

select distinct r2.* from 
table r1 
join table r2 
on
r1.linkedcustcode = r2.linkedcustcode and r1.salesperson <> r2.salesperson

Upvotes: 2

Related Questions