• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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
0
pda4me
Asked:
pda4me
  • 4
  • 4
1 Solution
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
pda4meAuthor Commented:
I like the union command...what is the x for at the end?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
pda4meAuthor Commented:
I still need one instance of the record inserted into Table4, will this do that?
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
pda4meAuthor Commented:
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
 
pda4meAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now