asked on

Combine 3 tables into a single table mysql and PHP

I have 4 tables: Table1, Table2, Table3 and Table4 all with the exact same structure/columns.

How do I formulate a SQL query to select ALL records and using the following columns from the first 3 tables, keep only one instance of any duplicate records and then insert into Table4?

Columns are: STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, CITY, ZIP_CODE

HainKurt

try:

insert into table 4(STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, CITY, ZIP_CODE)
select STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, CITY, ZIP_CODE from table1
union
select STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, CITY, ZIP_CODE from table2
union
select STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, CITY, ZIP_CODE from table3

HainKurt

union will eliminate duplicates... or you can use another select to get distinct if you have duplicates

insert into table4 (...)
select distinct * from (
select ... from table1
union
select ... from table2
union
select ... from table3
) x

pda4me

ASKER

I like the union command...what is the x for at the end?

pda4me

ASKER

I still need one instance of the record inserted into Table4, will this do that?

HainKurt

x is alias (may or may not be needed)
union generally selects unique/distinct records internally...
also (...) may be needed

insert into table4(column names here)
(
select columns here from table1
union
select columns here from table2
union
select columns here from table3
)

i don't have mysql installed that's why I give you alternatives :) I have sql, access and oracle installed...

HainKurt

according to the documentation and samples

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

(..) is not needed...

also "The default behavior for UNION is that duplicate rows are removed from the result." meaning we do not need extra select :)

ASKER CERTIFIED SOLUTION

pda4me

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pda4me

ASKER

I need an actual example, not references to documentation...its helpful to have the program installed (mysql) when providing a solution so you can test your answers first.