I have a table with accounts and payment type. Every account MUST have 1 payment type of 'GOGW', 1 account may have multiple payment types.

I need to find a way to identify which accounts don't have a 'GOGW' please

table1

should return

CodePudding user response：

You can use GROUP BY / HAVING to find out which accounts

SELECT Account
FROM table1
GROUP BY Account
HAVING COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) = 0

If you want to find those that does not have exactly one:

SELECT Account
FROM table1
GROUP BY Account
HAVING COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) <> 1

Now you can use IN, EXISTS or a JOIN to get the types for these accounts:

SELECT a.*
FROM table1 t1
JOIN (
    SELECT Account
    FROM table1
    GROUP BY Account
    HAVING COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) = 0
) AS t2
    ON t1.Account = t2.Account

If you are on a decently recent version, you can use a window function instead:

select Account, Type
from ( 
  select Account, Type, COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) OVER (PARTITION BY ACCOUNT) as cnt
  from table1
) as t
WHERE cnt = 0;

Fiddle

CodePudding user response：

you can try the following statement ...

SELECT table1.[Account]
      ,table1.[Type]
FROM table1
LEFT OUTER JOIN (SELECT table1.[Account]
FROM table1
WHERE table1.[Type] = 'GOGW') GOGW ON table1.[Account] = GOGW.[Account]
WHERE table1.[Type] <> 'GOGW'
AND GOGW.[Account] IS NULL