We have two tables and data similar as below
Table: MASTER
--------------------
Master_id - Dsite
--------------------
1 - NewYork
2 - Tokiyo
--------------------


Table: DETAILS
---------------------------------
Master_id - item_id - owner
---------------------------------
1 - id1 - James
1 - id1 - Eva
1 - id2 - Dave
1 - id3 - John
2 - id1 - Suzy
2 - id1 - Smith
2 - id4 - Ravi
---------------------------------

We want to list the result set as all those item_ids and owners for
which the item_id is associated with more than one masterids.

So the result for the above example would be as below -
Item_id - Owner
Id1 - James
Id1 - Eva
Id1 - suzy
Id1 - smith

How should one write SQL query for such scenario?

Thanks in advance for your help.

A few different methods here:

-- SQL Server 2005 only
SELECT item_id, owner
FROM (SELECT master_id,
item_id,
owner,
COUNT(*) OVER(
PARTITION BY item_id) AS cnt
FROM DETAILS) AS D
WHERE cnt > 1

-- SQL Server 2000
SELECT master_id,
item_id,
owner
FROM DETAILS
WHERE item_id IN (
SELECT item_id
FROM DETAILS
GROUP BY item_id
HAVING MAX(master_id) <> MIN(master_id))

-- SQL Server 2000
SELECT D.master_id,
D.item_id,
D.owner
FROM DETAILS AS D
JOIN (SELECT item_id
FROM DETAILS
GROUP BY item_id
HAVING COUNT(master_id) > 1) AS C
ON D.item_id = C.item_id

-- SQL Server 2000
SELECT master_id,
item_id,
owner
FROM DETAILS
WHERE item_id IN (
SELECT item_id
FROM DETAILS
GROUP BY item_id
HAVING COUNT(master_id) > 1)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Try something like this

Select item_id, owner
From Details
Where (Select Count(*)
From Details as DetailCount
Where DetailCount.item_Id = Detail.Item_Id) > 1


Daniel

"MadhavC" <choudharymv (AT) gmail (DOT) com> a écrit dans le message de news:
13f29221-f55b-492f-9f91-a9e46547ce8b...oglegroups.com...