How to show which client has multi of a certain field

1

Hello I have a clients benefits information as such:

Client number, Client Name, Payer, type

12345, Joe Smith, Medicaid, Primary
12345, Joe Smith, Aetna, Secondary
99988, Frank Jones, Medicaid, Primary
09090, Bob Roberts, Medicaid, Primary

In this example, I would like to report to show that Joe Smith has both a primary and a secondary payer, (actually there can be 3 also a tertiary).
So the report would be:
12345, Joe Smith, Medicaid, Primary
12345, Joe Smith, Aetna, Secondary

2

You can use EXISTS:

SELECT ...
FROM yourTable t
WHERE EXISTS (SELECT * FROM yourTable t2 WHERE t2.ClientNumber = t1.ClientNumber AND t2.Type <> 'Primary')

Or you can use IN:

SELECT ...
FROM yourTable t
WHERE t.ClientNumber IN (SELECT t2.ClientNumber FROM yourTable t2 WHERE t2.Type <> 'Primary')
2 Likes