Forum Stats

  • 3,734,027 Users
  • 2,246,861 Discussions
  • 7,857,001 Comments

Discussions

using minus

464733
464733 Member Posts: 233
edited January 2006 in SQL & PL/SQL
I have 3 similar tables from different sources, how can i list their differences

table1
typeid productname
1234 pencil
1234 pen
1123 paper
1234 clips
2938 rubber

table2
typeid productname
1234 pencil
1234 pen
1123 paper
1234 folder
1234 clips

table3
typeid productname
1234 pencil
2256 glue
9093 clipboard
1234 folder
1234 clips

select t1.typeid, t1.productname, t2.typeid, t2.productname, t3.typeid, t3.productname frm table1 t1, table2 t2, table3 t3
where
t1.typeid = t2.typeid and
t1.typeid = t3.typeid and
t2.typeid = t3.typeid and
t1.productname = t2.productname and
t1.productname = t3.productname and
t2.productname = t3.productname

this query will list all the items with the same typeid and productname, but how can i list all the others ??

thanks.

Comments

  • APC
    APC Member Posts: 11,316 Bronze Crown
    edited January 2006
    this query will list all the items with the same typeid and productname, but how can i list all the others ??
    One way of doing it, assuming all you want to do is identify items that only appear in one table...
    SQL> SELECT typeid, productname
    2 FROM (SELECT typeid, productname FROM table1
    3 UNION ALL
    4 SELECT typeid, productname FROM table2
    5 UNION ALL
    6 SELECT typeid, productname FROM table3)
    7 GROUP BY typeid, productname
    8 HAVING count(*) = 1
    9 /
    TYPEID PRODUCTNAME
    ---------- --------------------
    2256 glue
    2938 rubber
    9093 clipboard

    SQL>
    Cheers, APC
  • Dmytro Dekhtyaryuk
    Dmytro Dekhtyaryuk Member Posts: 1,290 Blue Ribbon
    APC,
    very nice solution !

    Regards
    Dmytro
  • 464733
    464733 Member Posts: 233
    this query will list all the items with the same
    typeid and productname, but how can i list all the
    others ??

    One way of doing it, assuming all you want to do is
    identify items that only appear in one table...
    SQL> SELECT typeid, productname
    2 FROM (SELECT typeid, productname FROM table1
    3 UNION ALL
    4 SELECT typeid, productname FROM
    OM table2
    5 UNION ALL
    6 SELECT typeid, productname FROM
    OM table3)
    7 GROUP BY typeid, productname
    8 HAVING count(*) = 1
    9 /
    TYPEID PRODUCTNAME
    ---------- --------------------
    2256 glue
    2938 rubber
    9093 clipboard

    SQL>
    Cheers, APC
    THANKS APC!!

    but can I have extra fields from the other 2 tables shown as well like this, If the other two tables do not have it, display a "NULL"

    t1.typeid t1.productname, t2.typeid t2.productname, t3.typeid t3.productname
    ---------------------------------------------------------------------------------------------------------------
    NULL NULL 2256 glue NULL NULL
    2938 rubber NULL NULL NULL NULL
    NULL NULL NULL NULL 9093 clipboard

    thanks.
  • 105967
    105967 Member Posts: 1,027
    In addition to the solution from APC you could also query the following (depending what is the definition of "differences"):
    select 'in table1 but not in table2' txt,
    typeid, productname
    from table1
    minus
    select 'in table1 but not in table2' txt,
    typeid, productname
    from table2
    union all
    select 'in table2 but not in table1' txt,
    typeid, productname
    from table2
    minus
    select 'in table2 but not in table1' txt,
    typeid, productname
    from table1
    union all
    select 'in table1 but not in table3' txt,
    typeid, productname
    from table1
    minus
    select 'in table1 but not in table3' txt,
    typeid, productname
    from table3
    union all
    select 'in table3 but not in table1' txt,
    typeid, productname
    from table3
    minus
    select 'in table3 but not in table1' txt,
    typeid, productname
    from table1
    union all
    select 'in table2 but not in table3' txt,
    typeid, productname
    from table2
    minus
    select 'in table2 but not in table3' txt,
    typeid, productname
    from table3
    union all
    select 'in table3 but not in table2' txt,
    typeid, productname
    from table3
    minus
    select 'in table3 but not in table2' txt,
    typeid, productname
    from table2
    /

    TXT TYPEID PRODUCTNAME
    --------------------------- ---------- ------------------------------
    in table1 but not in table2 2938 rubber
    in table1 but not in table3 1123 paper
    in table1 but not in table3 1234 pen
    in table1 but not in table3 2938 rubber
    in table2 but not in table1 1234 folder
    in table2 but not in table3 1123 paper
    in table2 but not in table3 1234 pen
    in table3 but not in table1 1234 folder
    in table3 but not in table1 2256 glue
    in table3 but not in table1 9093 clipboard
    in table3 but not in table2 2256 glue
    in table3 but not in table2 9093 clipboard

    12 rows selected
  • cd_2
    cd_2 Member Posts: 5,021
    edited January 2006
    Taking the approach a bit further (and hopefully shorter):
    SELECT tall.typeid, tall.productname, Max(t1) table1, Max(t2) table2, Max(t3) table3
    FROM (SELECT t1.typeid, t1.productname, 'X' t1, ' ' t2, ' ' t3
    FROM table1 t1
    UNION ALL
    SELECT t2.typeid, t2.productname, ' ' t1, 'X' t2, ' ' t3
    FROM table2 t2
    UNION ALL
    SELECT t3.typeid, t3.productname, ' ' t1, ' ' t2, 'X' t3
    FROM table3 t3
    ) tall
    GROUP BY tall.typeid, tall.productname
    HAVING Max(t1) <> 'X' OR Max(t2) <> 'X' OR Max(t3) <> 'X';
    C.

    Message was edited by:
    cd

    Made an even shorter version
  • Michel SALAIS
    Michel SALAIS Member Posts: 659 Blue Ribbon
    what if a row is common to two tables but not in the third one?
  • Michel SALAIS
    Michel SALAIS Member Posts: 659 Blue Ribbon
    but can I have extra fields from the other 2 tables
    shown as well like this, If the other two tables do
    not have it, display a "NULL"

    t1.typeid t1.productname, t2.typeid t2.productname,
    t3.typeid t3.productname
    ------------------------------------------------------
    ------------------------------------------------------
    ---
    NULL NULL 2256 glue NULL NULL
    2938 rubber NULL NULL NULL NULL
    NULL NULL NULL NULL 9093 clipboard
    I am not sure this is good!
    What if a row is common between two tables only?
    If this objection is relevant for you then here is a solution:
    SQL> select *
    2 from t1;

    TYPEID PRODUCTNAME
    ---------- --------------------
    1234 pencil
    1234 pen
    1123 paper
    1234 clips
    2938 rubber

    SQL>
    SQL> select *
    2 from t2;

    TYPEID PRODUCTNAME
    ---------- --------------------
    1234 pencil
    1234 pen
    1123 paper
    1234 clips
    1234 folder

    SQL>
    SQL> select *
    2 from t3;

    TYPEID PRODUCTNAME
    ---------- --------------------
    1234 pencil
    2256 glue
    9093 clipboard
    1234 clips
    1234 folder

    SQL>
    SQL> select t1.*, t2.*, t3.*
    2 from (t1 full join t2 on (t1.typeid = t2.typeid and t1.productname = t2.productname))
    3 full join t3 on (t1.typeid = t3.typeid and t1.productname = t3.productname
    4 or t3.typeid = t2.typeid and t3.productname = t2.productname)
    5 minus
    6 select *
    7 from t1, t2, t3
    8 where t1.typeid = t2.typeid
    9 and t1.productname = t2.productname
    10 and t1.typeid = t3.typeid
    11 and t1.productname = t3.productname;

    TYPEID PRODUCTNAME TYPEID PRODUCTNAME TYPEID PRODUCTNAME
    ---------- -------------------- ---------- -------------------- ---------- --------------------
    1123 paper 1123 paper
    1234 pen 1234 pen
    2938 rubber
    1234 folder 1234 folder
    2256 glue
    9093 clipboard

    6 rows selected.
  • 464733
    464733 Member Posts: 233
    thank you everyone !!!!!!
  • 464733
    464733 Member Posts: 233
    Hi Michel,

    If the field is blank, how can I insert a text such as "-------" to indicate the null field?

    thanks.
  • smilearun
    smilearun Member Posts: 17
    Use the below query.....


    select NVL(t1.TYPEID,'----'), NVL(t1.PRODUCTNAME,'----'), NVL(t2.TYPEID,'----'), NVL(t2.PRODUCTNAME,'----'),NVL(t3.TYPEID,'----'), NVL(t3.PRODUCTNAME,'----')
    from (t1 full join t2 on (t1.typeid = t2.typeid and t1.productname = t2.productname))
    full join t3 on (t1.typeid = t3.typeid and t1.productname = t3.productname
    or t3.typeid = t2.typeid and t3.productname = t2.productname)
    minus
    select *
    from t1, t2, t3
    where t1.typeid = t2.typeid
    and t1.productname = t2.productname
    and t1.typeid = t3.typeid
    and t1.productname = t3.productname;
  • Michel SALAIS
    Michel SALAIS Member Posts: 659 Blue Ribbon
    Hi,
    You have already a reply ...
    But if you are using SQL*Plus you have another choice:

    SET NULL '----'

    Then, any null value in SQL$Plus would be printed as '----'
  • 479054
    479054 Member Posts: 28
    Hi,
    I have made three tables ::

    TABA TABB TABC
    ID VALUE ID VALUE ID VALUE
    1 TOM 1 TOM 1 TOM
    2 DICK 10 DICK 2 SAM
    3 HARRY 3 HARRY 3 BOB


    The following is the query that you have suggested to list all the items with the same
    typeid and productname (value in this case).

    SELECT TABA.ID ID_A,
    TABA.VALUE VAL_A,
    TABB.ID ID_B,
    TABB.VALUE VAL_B,
    TABC.ID ID_C,
    TABC.VALUE VAL_C
    FROM TABA,TABB,TABC
    WHERE
    TABA.ID = TABB.ID and
    TABB.ID = TABC.ID and
    TABC.ID = TABA.ID and
    TABA.VALUE = TABB.VALUE and
    TABB.VALUE = TABC.VALUE and
    TABC.VALUE = TABA.VALUE ;

    The remaining you can get using the following query ::

    SELECT (SELECT ID
    FROM TABA
    WHERE TABA.ID=A.ID
    AND TABA.VALUE=A.VALUE) ID_A,
    (SELECT VALUE
    FROM TABA
    WHERE TABA.ID=A.ID
    AND TABA.VALUE=A.VALUE) VALUE_A,
    (SELECT ID
    FROM TABB
    WHERE TABB.ID=A.ID
    AND TABB.VALUE=A.VALUE) ID_B,
    (SELECT VALUE
    FROM TABB
    WHERE TABB.ID=A.ID
    AND TABB.VALUE=A.VALUE) VALUE_B,
    (SELECT ID
    FROM TABC
    WHERE TABC.ID=A.ID
    AND TABC.VALUE=A.VALUE) ID_C,
    (SELECT VALUE
    FROM TABC
    WHERE TABC.ID=A.ID
    AND TABC.VALUE=A.VALUE) VALUE_C
    FROM
    (SELECT TABA.id ,TABA.value FROM TABA
    UNION ALL
    SELECT TABB.id ,TABB.value FROM TABB
    UNION ALL
    SELECT TABC.id ,TABC.value FROM TABC) A
    WHERE A.ID NOT IN (SELECT TABA.id FROM TABA
    INTERSECT
    SELECT TABB.id FROM TABB
    INTERSECT
    SELECT TABC.id FROM TABC)
    OR A.VALUE NOT IN (SELECT TABA.VALUE FROM TABA
    INTERSECT
    SELECT TABB.VALUE FROM TABB
    INTERSECT
    SELECT TABC.VALUE FROM TABC) ;

    - Thanks
    Sandipan
This discussion has been closed.