SQL Select Duplicate Values based on Specific Condition-CodePudding

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

CodePudding user response：

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
)

CodePudding user response：

You can try out following Query.

I have user the table name as persons. Please use your table name to test this.

Select ID,Name,Type from persons INNER JOIN (Select Name as np from persons where Type="Personal") as mytable on mytable.np = persons.Name where name in (select name from persons group by name having count(distinct type)>1);

The logic behind this is,

First, we are searching the condition met rows which means the rows which are having Type = "Personal" and name it as "mytable". This part is done by following part of the query.

Select Name as np from persons where Type="Personal") as mytable

Then we take the inner join with the original table to grab the other duplicate values. As per the given table, Name is the column that is duplicated. Therefore we join both tables depending on the name value.This part is done by following part of the query.

persons INNER JOIN (Select Name as np from persons where Type="Personal") as mytable on mytable.np = persons.Name

To grab which have only duplicate Name values. Use following part in the query.

where name in (select name from persons group by name having count(distinct type)>1);

The resulting table contains all the records you needed with duplicate ones. Then grab only the needed columns as ID,Naem,Type.

Select ID,Name,Type

Thank you.

Hope you will find a solution to your problem.