Re: SQL query for this purpose

On Feb 4, 1:08 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Feb 4, 2: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.
>
> The setup:
> CREATE TABLE T1(
> MASTER_ID NUMBER(10),
> DSITE VARCHAR2(15));
>
> CREATE TABLE T2(
> MASTER_ID NUMBER(10),
> ITEM_ID VARCHAR2(10),
> OWNER VARCHAR2(15));
>
> INSERT INTO T1 VALUES (1,'NewYork');
> INSERT INTO T1 VALUES (2,'Tokiyo');
>
> INSERT INTO T2 VALUES (1,'ID1','James');
> INSERT INTO T2 VALUES (1,'ID1','Eva');
> INSERT INTO T2 VALUES (1,'ID2','Dave');
> INSERT INTO T2 VALUES (1,'ID3','John');
> INSERT INTO T2 VALUES (2,'ID1','Suzy');
> INSERT INTO T2 VALUES (2,'ID1','Smith');
> INSERT INTO T2 VALUES (2,'ID4','Ravi');
>
> The table T1 is not needed in this example.
>
> Let's try a simple experiment with GROUP BY and HAVING:
> SELECT
> MASTER_ID,
> ITEM_ID,
> COUNT(*) NUM
> FROM
> T2
> GROUP BY
> MASTER_ID,
> ITEM_ID
> HAVING
> COUNT(*)>1;
>
> MASTER_ID ITEM_ID NUM
> --------- ------- ---
> 1 ID1 2
> 2 ID1 2
>
> The people in those groups are the ones of interest.
>
> If we slide the above into an inline view, we can join back to the
> original table to pick up the extra columns of interest:
> SELECT
> T2.ITEM_ID,
> T2.OWNER
> FROM
> (SELECT
> MASTER_ID,
> ITEM_ID,
> COUNT(*) NUM
> FROM
> T2
> GROUP BY
> MASTER_ID,
> ITEM_ID
> HAVING
> COUNT(*)>1) V,
> T2
> WHERE
> V.MASTER_ID=T2.MASTER_ID
> AND V.ITEM_ID=T2.ITEM_ID;
>
> ITEM_ID OWNER
> ------- -----
> ID1 James
> ID1 Eva
> ID1 Suzy
> ID1 Smith
>
> Method #2, using analytical functions to make a single pass through
> the table:
> SELECT
> ITEM_ID,
> OWNER,
> COUNT(*) OVER (PARTITION BY MASTER_ID, ITEM_ID) NUM
> FROM
> T2;
>
> ITEM_ID OWNER NUM
> ------- ----------- ---
> ID1 James 2
> ID1 Eva 2
> ID2 Dave 1
> ID3 John 1
> ID1 Suzy 2
> ID1 Smith 2
> ID4 Ravi 1
>
> Now, we can slide the above into an inline view to retrieve only those
> rows with NUM greater than 1:
> SELECT
> ITEM_ID,
> OWNER
> FROM
> (SELECT
> ITEM_ID,
> OWNER,
> COUNT(*) OVER (PARTITION BY MASTER_ID, ITEM_ID) NUM
> FROM
> T2)
> WHERE
> NUM>1;
>
> ITEM_ID OWNER
> ------- -----
> ID1 James
> ID1 Eva
> ID1 Suzy
> ID1 Smith
>
> On a side note, this request looks similar to an interview question
> that Daniel Morgan posted to the group about a year ago.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Charles,
using the methods you posted I notice that adding an entry in T2 ( insert into T2 values (1,'ID2','Mauro') ) we get also entries of item_id ID2, is this the expected behaviour? Aren't we looking for item_id associated with more than one distinct masterid (I supposed it was implicit) ?
Regards
Mauro Received on Mon Feb 04 2008 - 06:49:24 CST