Re: SQL query for this purpose

From: <mauro.pagano_at_gmail.com>
Date: Mon, 4 Feb 2008 01:43:07 -0800 (PST)
Message-ID: <5d90be4a-cc3f-42ef-b040-5ef3e8281a33@e6g2000prf.googlegroups.com>

On Feb 4, 8:05 am, MadhavC <choudhar..._at_gmail.com> wrote:
> 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.

Hi,
I'm not sure if I've completely understood your requirement, probably an answer could be:

select item_id, owner
  from details
 where item_id in (SELECT distinct a.item_id   FROM (SELECT item_id, master_id

          FROM details) a,
       (SELECT item_id, master_id
          FROM details) b

 WHERE a.item_id = b.item_id AND a.master_id != b.master_id)

Obviously there're other methods to resolve your request, probably better in performance too.
I hope it helps
Regards
Mauro Received on Mon Feb 04 2008 - 03:43:07 CST