CODEWITHSUNDEEP
sql
user3127999
user3127999

Reputation: 21

Creating a table that includes only records that exist in one table without existing in another

I'm a newbie here and hoping someone can help with this sql. I've created two tables, one of which holds EVERY record, another which contains the records that I DON'T want in my table. I tried joining them in the way I researched that is supposed to work, to include only records where they ARE NOT In the second table, but I'm getting an error. The SQL is: 

Create table t3 as 
(Select * from t1 
 Left Outer join t2 
 on (t1.ID = t2.Orig_ID and t1.ID_Line = t2.Orig_ID_Line) 
 Where t2.Orig_ID is null 
 and t2.Orig_ID_Line is null)

This should be simple. However, i'm getting an error that says "Duplicate column name in Orig_ID"

HELP! Thanks.

Upvotes: 2

Views: 57

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 754160

You were very close with your original statement, but forgot to limit the columns to those from t1, so you had twice as many columns as intended. Try:

CREATE TABLE t3 AS 
    (SELECT t1.* FROM t1         -- Key change * -> t1.*
       LEFT OUTER JOIN t2 
         ON (t1.ID = t2.Orig_ID AND t1.ID_Line = t2.Orig_ID_Line) 
      WHERE t2.Orig_ID IS NULL 
        AND t2.Orig_ID_Line IS NULL)

Upvotes: 1

Bren
Bren

Reputation: 2214

Try this

Create table t3 as 

select * from t1 where (t1.ID, t1.ID_Line) not in ( select t2.ID, t2.Orig_ID_Line from t2 where t2.ID is not null and t2.Orig_ID_Line is not null )

You get duplicate column name error, because you join and select both fields in both tables. So same column names return from the query and table can not have same column names. What you need is to select columns only in t1 table 

select t1.* ......

But you do not need join operation. What you need is simple. Using "not in" operator is what you need. Have a look at an sql tutorial for in/not in operators.

Upvotes: 0

Related Questions