query with join of 3 tables.

Question

I want a single query to produce the following results..

To result in records that are in table1 and in table2 and were not in table3.

There are more than 10,000 records in each table..so i am looking for an efficient one. In all the table Cono is the primary key..

In detail with tables.

TABLE 1:-

Cono     

th-123
th-124
th-125

TABLE 2:-

Cono     

th-234
th-245
th-256

TABLE 3:-

Cono     

th-124
th-125
th-256

Now i want to have the following records

Result TABLE:-

Cono     

th-123
th-234
th-245

Answer 1

Try this

WITH Table1 AS
(
    SELECT 'th-123' CONO UNION
    SELECT 'th-124' UNION
    SELECT 'th-125'
)
,
Table2 AS
(
    SELECT 'th-234' CONO UNION
    SELECT 'th-245' UNION
    SELECT 'th-256'
)
,
Table3 AS
(
    SELECT 'th-124' CONO UNION
    SELECT 'th-125' UNION
    SELECT 'th-256'
)

SELECT CONO
FROM Table1 
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table3
    WHERE TABLE1.CONO = TABLE3.CONO
)

UNION ALL

SELECT CONO
FROM Table2
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table3
    WHERE TABLE2.CONO = TABLE3.CONO
)

Answer 2

try this:

select t.cono from Table1 t WHERE NOT EXISTS (SELECT 1 
                                              FROM Table3 x WHERE x.cono=t.cono)
UNION
select t.cono from Table2 t WHERE NOT EXISTS (SELECT 1 
                                              FROM Table3 x WHERE x.cono=t.cono)

Answer 3

Try this (not tested):

; WITH all_data AS (
   SELECT * FROM table1
   UNION ALL
   SELECT * FROM table2
)
SELECT * 
FROM all_data ad
WHERE NOT EXISTS (
   SELECT *
   FROM table3 t3
   WHERE ad.Cono = t3.Cono);

Greets Flo

Answer 4

Kind of vague tables and names, but here's what you can do if you REALLY wanna do it in one query:

SELECT Cono
FROM Table3
WHERE NOT EXISTS ( SELECT Cono
                   FROM TABLE1 as T
                   WHERE EXISTS ( SELECT *
                                  FROM TABLE2
                                  WHERE T.Cono = TABLE2.Cono));

This should select the values in table 3 that do not exist in the query that was created in parenthesis, which is a table made up of rows that are in tables 1 and 2.

Unfortunately, nesting and efficiency normally don't go hand-in-hand...

Answer 5

(SELECT t1.Cono FROM table1 t1
LEFT JOIN table3 t3
ON t3.Cono = t1.Cono
WHERE t3.Cono IS NULL)
UNION ALL
(SELECT t2.Cono FROM table2 t2
LEFT JOIN table3 t3
ON t3.Cono = t2.Cono
WHERE t3.Cono IS NULL)

Answer 6

this one worked for me... and process fast:

select X.FID, c.id as CID 
from
(
    select a.id as FID from tbl1 a
    union 
    select b.id as FID from tbl2 b
) as X
 left outer join tbl3 c on FID = c.id
where
    c.id is null
;