CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Bob Ryder
Bob Ryder

Reputation: 57

SQL: Query to identify instances of an associated row in one table

I'm having some trouble writing a query.

For example:

Let's say I have just one table with the following values:

    Items
+-----+--------+
| ID  |  NAME  |
+-----+--------+
| A1  | Item_1 |
| A1  | Item_2 |
| A1  | Item_3 |
| A2  | Item_1 |
| A2  | Item_2 |
| A3  | Item_1 |
+-----+--------+

From this, I want to identify all of the item names that are associated with more than one ID, along with the associated ID names.

Given this example the output would be --

+----+--------+
| ID |  Name  |
+----+--------+
| A1 | Item_1 |
| A2 | Item_1 |
| A3 | Item_1 |
| A1 | Item_2 |
| A2 | Item_2 |
+----+--------+

Item_3 would be excluded since there is only one instance of it, associated with A3.

I'm using SQL Server 2008. Thanks in advance!

Upvotes: 1

Views: 90

Answers (3)

shanmugamgsn
shanmugamgsn

Reputation: 840

Try this

SELECT id,name
FROM Table1
where name in ( select name from(
select name,count(name) as cnt from table1
group by name)
where cnt>1)

This query was written in MS Access / Oracle try to convert into SQL. I don't know syntax for those.

I will explain logic from inner most query First you are taking names which are greater than count 1 Then you are selecting your required id's and names.

The most simplest form of above is as follows

SELECT id,name
FROM Table1 
where name in (
  select name as cnt from table1
  group by name
  having count(name)>1)

Upvotes: 0

Andriy M
Andriy M

Reputation: 77687

You could use windowed aggregating:

WITH Counted AS (
  SELECT
    ID,
    NAME,
    IDCount = COUNT(*) OVER (PARTITION BY NAME)
  FROM atable
)
SELECT
  ID,
  NAME
FROM Counted
WHERE IDCount > 1

References:

Upvotes: 0

Yahia
Yahia

Reputation: 70369

use

SELECT * FROM MyTable A WHERE A.Name IN
(SELECT T.Name FROM MyTable T GROUP BY T.Name HAVING COUNT(DISTINCT T.ID) > 1)
ORDER BY A.Name, A.ID

Upvotes: 1

Related Questions