CODEWITHSUNDEEP
sqloracle-database
user1365420
user1365420

Reputation: 61

How to get data from three or more tables

I need to display distinct data between three tables. How to do this requirement.

FirstTable: 

9999999999                     
8888888888           
7777777777
6666666666
5555555555

SecondTable:

7777777777
9999999999

ThirdTable:

8888888888

i want output in this format.

6666666666
5555555555

Upvotes: 1

Views: 111

Answers (6)

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

Contrary to popular inclination, I think this is a good question!

The following solution finds all unique values in three tables:

with middle_tab as(
select t1.id t1_id,t2.id t2_id,t3.id t3_id from tab1 t1
full outer join tab2 t2 on t1.id=t2.id
full outer join tab3 t3 on t1.id=t3.id
 )

  select coalesce(t1_id,t2_id,t3_id) unique_ids from (
select t1_id,t2_id,t3_id,NVL2(t1_id,0,1)+NVL2(t2_id,0,1)+NVL2(t3_id,0,1) b
 from middle_tab)
where b>1

Here is SQL Fiddle Demo!

Upvotes: 0

Sandeep Kumar
Sandeep Kumar

Reputation: 803

YOUR result doesn't show distinct data. It shows the data which is not IN on any OTHER table

So you can write like this

    CREATE TABLE FirstTable (VALUE VARCHAR(10))

    CREATE TABLE SecondTable (VALUE VARCHAR(10))

    CREATE TABLE ThirdTable (VALUE VARCHAR(10))

    INSERT INTO FirstTable VALUES(9999999999)                     
    INSERT INTO FirstTable VALUES(8888888888)
    INSERT INTO FirstTable VALUES(7777777777)
    INSERT INTO FirstTable VALUES(6666666666)
    INSERT INTO FirstTable VALUES(5555555555)


    INSERT INTO SecondTable VALUES(9999999999)                     
    INSERT INTO SecondTable VALUES(7777777777)

    INSERT INTO ThirdTable VALUES(8888888888)

    SELECT a1.vALUE FROM (SELECT a.vALUE FROM FirstTable a 
        LEFT OUTER JOIN SecondTable b ON A.VALUE=B.VALUE
            WHERE   b.VALUE IS NULL) a1
        LEFT OUTER JOIN ThirdTable c ON  A1.VALUE=C.VALUE
        WHERE c.VALUE IS NULL

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

For the data you gave us, you can try this:

    select YourColumn from Table1
minus
    select Yourcolumn from Table2
minus
    select YourColumn from Table3

This however wouldn't give you entries that existed in Table 3 but not tables 1 ND 2. I second the suggestion that you improve the question.

Upvotes: 1

Himanshu
Himanshu

Reputation: 32602

Use LEFT JOIN

   SELECT T1."Col"
     FROM Table1 T1
LEFT JOIN Table2 T2 
       ON T1."Col" = T2."Col"
LEFT JOIN Table3 T3 
       ON T1."Col" = T3."Col"
    WHERE T2."Col" IS NULL 
      AND T3."Col" IS NULL

Output:

|        COL |
--------------
| 6666666666 |
| 5555555555 |

See this SQLFiddle

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Try:

SELECT Column FROM FirstTable
WHERE Column NOT IN
    (SELECT Column from SecondTable UNION 
    SELECT Column from ThirdTable)

Upvotes: 0

Shahid Iqbal
Shahid Iqbal

Reputation: 2135

try this...

Select yourColumn 
from Table1 
where yourColumn  not in
   ( select yourColumn  from Table2) 
and yourColumn  not in 
  (select yourColumn  from table3)

Upvotes: 0

Related Questions