SQL - Find records with similar value

Question

Hello,

I have a table called "Tasks". This table has the following fields

• ID
• TypeID
• Description

How do I find all of the tasks that have more than one task of a TypeID? I do not have a TypeID to query with. Rather, I just want to list that tasks that have a TypeID that is used across multiple tasks. I'm not sure how to do this.

Thank you for your help!

Answer 1

Use:

  SELECT t.description
    FROM TASKS t
GROUP BY t.description
  HAVING COUNT(t.typeid) > 1

Answer 2

SELECT
    *
FROM
    Tasks
WHERE TypeID IN
    (SELECT TypeID FROM Tasks
     GROUP BY TypeID HAVING COUNT(*) > 1)

Answer 3

Here are two ways to do it without using group by:

# faster solution
SELECT A.*
FROM Tasks A,
     Tasks B
WHERE B.id != A.id AND B.TypeID = A.TypeID

OR

SELECT A.*
FROM Tasks A,
WHERE EXISTS ( SELECT 1 FROM Tasks B WHERE B.id != A.id AND B.TypeID = A.TypeID)