CODEWITHSUNDEEP
sql
user7403515
user7403515

Reputation:

SQL query to check whether multiple entries exists

I have a table det with the following columns:

Name  ID
--------
A     1
B     2
C     1
D     3

I need a query to check if multiple entry of name is there for single id. Here I am expecting a result like this:

Name  ID
--------
A     1
c     1

Upvotes: 0

Views: 1812

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would just use exists:

select d.*
from det d
where exists (select 1 from det d2 where d2.id = d.id and d2.name <> d.name);

Upvotes: 0

Alexey S. Larionov
Alexey S. Larionov

Reputation: 7927

Use a subquery where you select relevant ID's that have multiple names

SELECT Name, ID
FROM det
WHERE ID IN (
  SELECT ID
  FROM det
  GROUP BY ID
  HAVING COUNT(Name) > 1
);

Upvotes: 0

Karthik Radhakrishnan
Karthik Radhakrishnan

Reputation: 944

you can use a simple select query with group by

SELECT ID, count(*) from det group by ID having count(*) >1

Upvotes: 2

Related Questions