mysql diff 2 columns in separate tables into new table

Hi

I have two tables with different structure but there are two columns with similar values.

I want to compare values in column C1 in Table T1 with values column C2 in Table T2 and create a new Table T3 that has the values that are unique to T1.C1 (values that are only in T1.C1 and NOT in T2.C2)

The columns to compare in T1 and T2 are both varchar type length 255 and indexed.

Thanks!
netsltAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
SELECT DISTINCT C1  INTO T3
FROM T1 s
WHERE NOT EXISTS (SELECT C2 FROM T2 d WHERE d.C2=s.C1)
0
netsltAuthor Commented:
thanks.

this gives me "undeclared variable: T3" error from mysql

T3 is a valid tablename.

any idea why this error occurs?

and what structure should t3 have? just one column of type T1 or T2?
0
PortletPaulfreelancerCommented:
MySQL doesn't support that exact "select into" syntax, but has a similar approach.
see: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

INSERT INTO T3 (C1)
    SELECT DISTINCT C1
    FROM T1 AS s
    WHERE NOT EXISTS (SELECT 1 FROM T2 AS d WHERE d.C2=s.C1)

nb: it is not necessary for that subquery to return the field C2, or any field actually, "select 1" is sufficient, but you can use "(SELECT C2 FROM T2 d WHERE d.C2=s.C1)" if that is more understandable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
netsltAuthor Commented:
Great - this works fine on my system!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.