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@gmail.com> a écrit dans le message de news:
13f29221-f55b-492f-9f91-a9e46547ce8b...oglegroups.com...
>
> 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.