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
)