SQL Select Duplicate Values based on Specific Condition

I am asking for a solution where I can find duplicate entries in a table but one of those duplicate entries MUST contain a specific value otherwise it won’t come in the result.

For example, my table is like this:

ID	Name	Type
1	Hassan	Commercial
2	Ahmed	Personal
3	Jack	Personal
4	Hassan	Commercial
5	Jack	Commercial
6	Charlie	Commercial

Expected result:

ID	Name	Type
3	Jack	Personal
5	Jack	Commercial

See, I do not want Id No. 1 & 4 as they do not have ‘Personal’ Value in their ‘Type’ Column.

But, Id No. 3 & 5 match my requirement.

I have tried the following query:

SELECT ID
 , Name
 , Type FROM (
  SELECT ID, Name, Type,
         COUNT(*) OVER (PARTITION BY Name) AS cnt
  FROM THIS_TABLE) AS t
WHERE t.cnt > 1

   

Thanks in Advance

Answer

Sounds like what you want is to first get the names of people with multiple account types

select name from this_table group by name having count(distinct type)>1

then dump the table for records matching those names.

select * from this_table where name in (
  select name from this_table group by name having count(distinct type)>1
)