Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Need SQL foo
November 6, 2007, 8:56 pm
rate this thread
I am working on merging two tables from two different databases which
both have a different number of fields. Both tables have the same
information, for a large number of records.
Table1 has fields 'foo_id' and 'bar' (where foo_id is the PK id)
Table2 has fields 'foo_id', 'bar', 'bat', and 'baz' (foo_id is the PK
is here, too)
There are likely to be multiple identical records in the 'bar' fields
between the tables, though the only 'foo_id' I need to keep is from
Table2 (or, more simply, all I need to care about from Table1 is the
I need to do a SELECT INTO into a 3rd table in a 3rd database. The
table in this 3rd table matches the structure of Table2.
TIA for any help
Re: Need SQL foo
You haven't told us the platform and just what level of SQL support
A view that's a UNION of the PK column(s) from both, then DISTINCT on
Left join both tables onto that. Select bat & baz from #2. Select bar
from either #2 (if not null) or else from #1 (using CASE)
Re: Need SQL foo
I've read Andy's post and I must say Andy knows a lot about about SQL.
I'm a simpler guy and would just do this:
(Assuming same database)
SELECT DISTINCT Table_1.bar,Table_2.bat,Table_2.baz ... FROM
Table_1,Table_2 WHERE Table_1.foo_id = Table_2.foo_id
Now, if they actually are seperate databases and not just seperate
tables, I would move all the data so it is on the same database.
I would consider learning some lessons from this about setting up
tables. You never want to have the same data in more than one table. All
you want is a common field to tie them together.