SQL & PL/SQL

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

APC,
very nice solution !

Regards
Dmytro

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.

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

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

what if a row is common to two tables but not in the third one?

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.

thank you everyone !!!!!!

Hi Michel,

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

thanks.

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;

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 '----'

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