?
Solved

Combine 3 tables into a single table mysql and PHP

Posted on 2011-10-12
8
Medium Priority
?
284 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:pda4me
  • 4
  • 4
8 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 36957138
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

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36957152
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

0
 

Author Comment

by:pda4me
ID: 36957186
I like the union command...what is the x for at the end?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:pda4me
ID: 36957203
I still need one instance of the record inserted into Table4, will this do that?
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36957329
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...
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36957348
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 :)
0
 

Accepted Solution

by:
pda4me earned 0 total points
ID: 36962335
I was looking for an actual sample like this:

INSERT INTO TABLE4(MLS_ID, MLS_STATE_ID, MLS_LISTING_ID, TLN_FIRM_ID, MLS_OFFICE_NAME, MLS_OFFICE_PHONE, TLN_REALTOR_ID, MLS_AGENT_NAME, MLS_AGENT_PHONE, LISTING_DATE, LISTING_EXPIRATION_DATE, SOLD_DATE, AVAILABLE_DATE, PROPERTY_TYPE_CODE, PROP_TYPE_DESCRIPTION, REMARKS,STATUS_CODE, SALE_PRICE, SOLD_PRICE, PROPERTY_STATE_ID, STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, LONGITUDE, LATITUDE, CITY, CITY_ID, ZIP_CODE, ZIP_PLUS4, MLS_AREA, COUNTY, FIPS_COUNTY_CODE, SUBDIVISION, COMMUNITY_NAME, YEAR_BUILT, ACRES, LOT_DIMENSIONS, LOT_SQUARE_FOOTAGE, LOT_SQUARE_FOOTAGE_LAND, BUILDING_SQUARE_FOOTAGE, BEDROOMS, BATHS_TOTAL, BATHS_FULL, BATHS_HALF, BATHS_THREE_QUARTER, FIREPLACE_NUMBER, TOTAL_ROOMS, SCHOOL_DISTRICT, SCHOOL_ELEMENTARY, SCHOOL_MIDDLE, SCHOOL_JUNIOR_HIGH, SCHOOL_HIGH, TOTAL_UNITS, TOTAL_BUILDINGS, TOTAL_LOTS, HOA_FEES, OWNERS_NAME, LEGAL, APN, TAXES, TAX_YEAR, SECTION, RANGE, TOWNSHIP, RENT_ON_SEASON, RENT_OFF_SEASON, PHOTO_IND, LAST_MLS_UPDATE_DATE, MASTER_BED, BED2, BED3, BED4, BED5, KITCHEN, BREAKFAST, LAUNDRY, DEN, DINING, FAMILY, LIVING, GREAT, EXTRA, FEATURE_CODES, MLS_OFFICE_ID, MLS_AGENT_ID, VIRTUAL_TOUR_URL, PHOTO_QUANTITY, PHOTO_URL, PHOTO_MOST_RECENT_DATE, AVM, BLOGGING, HOA_FREQUENCY, f95)
    SELECT   street_number,
             street_name,
             street_type,
             street_direction,
             unit_number,
             city,
             zip_code
        FROM (SELECT MLS_ID, MLS_STATE_ID, MLS_LISTING_ID, TLN_FIRM_ID, MLS_OFFICE_NAME, MLS_OFFICE_PHONE, TLN_REALTOR_ID, MLS_AGENT_NAME, MLS_AGENT_PHONE, LISTING_DATE, LISTING_EXPIRATION_DATE, SOLD_DATE, AVAILABLE_DATE, PROPERTY_TYPE_CODE, PROP_TYPE_DESCRIPTION, REMARKS,STATUS_CODE, SALE_PRICE, SOLD_PRICE, PROPERTY_STATE_ID, STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, LONGITUDE, LATITUDE, CITY, CITY_ID, ZIP_CODE, ZIP_PLUS4, MLS_AREA, COUNTY, FIPS_COUNTY_CODE, SUBDIVISION, COMMUNITY_NAME, YEAR_BUILT, ACRES, LOT_DIMENSIONS, LOT_SQUARE_FOOTAGE, LOT_SQUARE_FOOTAGE_LAND, BUILDING_SQUARE_FOOTAGE, BEDROOMS, BATHS_TOTAL, BATHS_FULL, BATHS_HALF, BATHS_THREE_QUARTER, FIREPLACE_NUMBER, TOTAL_ROOMS, SCHOOL_DISTRICT, SCHOOL_ELEMENTARY, SCHOOL_MIDDLE, SCHOOL_JUNIOR_HIGH, SCHOOL_HIGH, TOTAL_UNITS, TOTAL_BUILDINGS, TOTAL_LOTS, HOA_FEES, OWNERS_NAME, LEGAL, APN, TAXES, TAX_YEAR, SECTION, RANGE, TOWNSHIP, RENT_ON_SEASON, RENT_OFF_SEASON, PHOTO_IND, LAST_MLS_UPDATE_DATE, MASTER_BED, BED2, BED3, BED4, BED5, KITCHEN, BREAKFAST, LAUNDRY, DEN, DINING, FAMILY, LIVING, GREAT, EXTRA, FEATURE_CODES, MLS_OFFICE_ID, MLS_AGENT_ID, VIRTUAL_TOUR_URL, PHOTO_QUANTITY, PHOTO_URL, PHOTO_MOST_RECENT_DATE, AVM, BLOGGING, HOA_FREQUENCY, f95 FROM TABLE1
              UNION
              SELECT MLS_ID, MLS_STATE_ID, MLS_LISTING_ID, TLN_FIRM_ID, MLS_OFFICE_NAME, MLS_OFFICE_PHONE, TLN_REALTOR_ID, MLS_AGENT_NAME, MLS_AGENT_PHONE, LISTING_DATE, LISTING_EXPIRATION_DATE, SOLD_DATE, AVAILABLE_DATE, PROPERTY_TYPE_CODE, PROP_TYPE_DESCRIPTION, REMARKS,STATUS_CODE, SALE_PRICE, SOLD_PRICE, PROPERTY_STATE_ID, STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, LONGITUDE, LATITUDE, CITY, CITY_ID, ZIP_CODE, ZIP_PLUS4, MLS_AREA, COUNTY, FIPS_COUNTY_CODE, SUBDIVISION, COMMUNITY_NAME, YEAR_BUILT, ACRES, LOT_DIMENSIONS, LOT_SQUARE_FOOTAGE, LOT_SQUARE_FOOTAGE_LAND, BUILDING_SQUARE_FOOTAGE, BEDROOMS, BATHS_TOTAL, BATHS_FULL, BATHS_HALF, BATHS_THREE_QUARTER, FIREPLACE_NUMBER, TOTAL_ROOMS, SCHOOL_DISTRICT, SCHOOL_ELEMENTARY, SCHOOL_MIDDLE, SCHOOL_JUNIOR_HIGH, SCHOOL_HIGH, TOTAL_UNITS, TOTAL_BUILDINGS, TOTAL_LOTS, HOA_FEES, OWNERS_NAME, LEGAL, APN, TAXES, TAX_YEAR, SECTION, RANGE, TOWNSHIP, RENT_ON_SEASON, RENT_OFF_SEASON, PHOTO_IND, LAST_MLS_UPDATE_DATE, MASTER_BED, BED2, BED3, BED4, BED5, KITCHEN, BREAKFAST, LAUNDRY, DEN, DINING, FAMILY, LIVING, GREAT, EXTRA, FEATURE_CODES, MLS_OFFICE_ID, MLS_AGENT_ID, VIRTUAL_TOUR_URL, PHOTO_QUANTITY, PHOTO_URL, PHOTO_MOST_RECENT_DATE, AVM, BLOGGING, HOA_FREQUENCY, f95 FROM TABLE2
              UNION
              SELECT MLS_ID, MLS_STATE_ID, MLS_LISTING_ID, TLN_FIRM_ID, MLS_OFFICE_NAME, MLS_OFFICE_PHONE, TLN_REALTOR_ID, MLS_AGENT_NAME, MLS_AGENT_PHONE, LISTING_DATE, LISTING_EXPIRATION_DATE, SOLD_DATE, AVAILABLE_DATE, PROPERTY_TYPE_CODE, PROP_TYPE_DESCRIPTION, REMARKS,STATUS_CODE, SALE_PRICE, SOLD_PRICE, PROPERTY_STATE_ID, STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, LONGITUDE, LATITUDE, CITY, CITY_ID, ZIP_CODE, ZIP_PLUS4, MLS_AREA, COUNTY, FIPS_COUNTY_CODE, SUBDIVISION, COMMUNITY_NAME, YEAR_BUILT, ACRES, LOT_DIMENSIONS, LOT_SQUARE_FOOTAGE, LOT_SQUARE_FOOTAGE_LAND, BUILDING_SQUARE_FOOTAGE, BEDROOMS, BATHS_TOTAL, BATHS_FULL, BATHS_HALF, BATHS_THREE_QUARTER, FIREPLACE_NUMBER, TOTAL_ROOMS, SCHOOL_DISTRICT, SCHOOL_ELEMENTARY, SCHOOL_MIDDLE, SCHOOL_JUNIOR_HIGH, SCHOOL_HIGH, TOTAL_UNITS, TOTAL_BUILDINGS, TOTAL_LOTS, HOA_FEES, OWNERS_NAME, LEGAL, APN, TAXES, TAX_YEAR, SECTION, RANGE, TOWNSHIP, RENT_ON_SEASON, RENT_OFF_SEASON, PHOTO_IND, LAST_MLS_UPDATE_DATE, MASTER_BED, BED2, BED3, BED4, BED5, KITCHEN, BREAKFAST, LAUNDRY, DEN, DINING, FAMILY, LIVING, GREAT, EXTRA, FEATURE_CODES, MLS_OFFICE_ID, MLS_AGENT_ID, VIRTUAL_TOUR_URL, PHOTO_QUANTITY, PHOTO_URL, PHOTO_MOST_RECENT_DATE, AVM, BLOGGING, HOA_FREQUENCY, f95 FROM TABLE3) t
    GROUP BY street_number,
             street_name,
             street_type,
             street_direction,
             unit_number,
             city,
             zip_code
0
 

Author Closing Comment

by:pda4me
ID: 36984581
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.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question