Re: SQL query for this purpose

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. Received on Mon Feb 04 2008 - 06:08:44 CST