Creating a table from other tables

Hunky · #1 (**permalink**) 08-07-09, 02:24

Hello everyone,

I have tw0 tables and both have an ID field in them.

Say table 1 contains ID's 1,2,3,4,5,6,7,8,9
and
Table 2 contains ID's 2,3,4,5,6

Now i want to create a new table from these tables which should have an ID field with the ID's which are present in Table 1 and NOT in Table 2.

Such that Table 3 contains the following entries

ID's - 1,7,8,9

each entry should be unique and not repeated in Table 3

In short
Table 3 = Table 1 - Table 2

Any help is appreciated..
Thanks in advance...

aflorin27 · #2 (**permalink**) 08-07-09, 03:14

SELECT *
FROM table1
WHERE ID NOT IN
(SELECT ID FROM table2)

r937 · #3 (**permalink**) 08-07-09, 06:03

Code:

CREATE TABLE table3
SELECT table1.*
  FROM table1
LEFT OUTER
  JOIN table2
    ON table2.ID = table1.ID
 WHERE table2.ID IS NULL

Internet Services

Web Development

Digital Marketing

Consumer Tech