sqlsql-server

How can I pull somebody with just a payer order 9 in payer order column?


CustomerID payerorder payername
00001 1 root
00001 2 spade
00001 9 ginny
00002 1 spade
00002 3 root
00002 9 ginny
00003 9 ginny

Result I would like to have:

CustomerID payerorder payername
000003 9 ginny

I am trying to pull customers that only have a payer order 9 payer name ginny, they can not have any other payer order attached to them. The issue I am running into is all customers have a payer order 9 payer name ginny. As you can see the billing order is not always a simple 1-8 listing, as depending how it is put into the system, they may not use billing order 2 but skip to billing order 3. How would I go about grabbing just customers with billing order 9 payer name ginny? I would say the ginny isnt needed in the sql due to payer order 9 will always be payer name ginny.

I did try doing:

WHERE payerorder < 9 AND payername is NULL 

Which ended up not working, it would only pull one row that had a payer order but a blank payer name but the customer, when I checked the profile, still had other payer orders like 1,3,5 with payer names.

I also tried:

payerorder = 9 and payername = ginny 

But the issue with that is everybody has payer order 9 payer name ginny. I only need to pull customers who only have payer order 9 and payer name ginny.

Edit: new post with updated information


Solution

  • One method would be to use an EXISTS to check no rows for that client exist with a value other than 9:

    SELECT DISTINCT
           YT.CustomerID,
           YT.payerorder,
           YT.payername
    FROM dbo.YourTable YT
    WHERE YT.payerorder = 9
      AND NOT EXISTS (SELECT 1
                      FROM dbo.YourTable E
                      WHERE E.payername= YT.payername
                        AND E.payerorder <> 9);
    

    An alternative method would be with a HAVING to check the MIN and MAX values are both 9:

    SELECT MAX(YT.CustomerID_,
           MAX(YT.payerorder) AS payerorder,
           YT.payername AS payername
    FROM dbo.YourTable YT
    GROUP BY YT.payername
    WHERE MAX(YT.payerorder) = 9
      AND MIN(YT.payerorder) = 9;