Re: SQL query for this purpose

On Feb 4, 7:49 am, mauro.pag..._at_gmail.com wrote:
> 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

The SQL statement that I posted provides the "right" output given the input that the OP supplied, so it must be right?

Very good catch Mauro, you are correct. Thanks for checking, and for pointing out that there is a logic error in the example. The "fixed" example SQL statements look like this:
SELECT
  ITEM_ID,
  COUNT(DISTINCT MASTER_ID) NUM
FROM
  T2
GROUP BY
  ITEM_ID
HAVING
  COUNT(DISTINCT MASTER_ID)>1; ITEM_ID NUM
---------- ----------

ID1                 2

SELECT
  T2.ITEM_ID,
  T2.OWNER
FROM
  (SELECT
    ITEM_ID,
    COUNT(DISTINCT MASTER_ID) NUM
  FROM
    T2
  GROUP BY
    ITEM_ID
  HAVING
    COUNT(DISTINCT MASTER_ID)>1) V,
  T2
WHERE
  V.ITEM_ID=T2.ITEM_ID; ITEM_ID OWNER
---------- ---------------

ID1        James
ID1        Eva
ID1        Suzy
ID1        Smith

ITEM_ID    OWNER                  NUM
---------- --------------- ----------
ID1        James                    2
ID1        Eva                      2
ID2        Dave                     2
ID2        Mauro                    2
ID3        John                     1
ID1        Smith                    2
ID1        Suzy                     2
ID4        Ravi                     1

SELECT
  ITEM_ID,
  OWNER
FROM
  (SELECT
    ITEM_ID,
    OWNER,
    COUNT(DISTINCT MASTER_ID) OVER (PARTITION BY ITEM_ID) NUM   FROM
    T2)
WHERE
  NUM>1; ITEM_ID OWNER
---------- ---------------

ID1        James
ID1        Eva
ID1        Suzy
ID1        Smith

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Feb 04 2008 - 07:13:03 CST