?
Solved

MySQL query combine 3 tables into 1

Posted on 2011-10-12
10
Medium Priority
?
288 Views
Last Modified: 2012-06-22
I need the actual SQL command to perform the following operation using MySQL 5.x.

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 from the first 3 tables and using the following columns from the first 3 tables, keep only one instance of any duplicate records and then insert that record and all other records into Table4?

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

Actual columns are: ID, STATE, NAME, STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER, CITY, ZIP_CODE
0
Comment
Question by:pda4me
  • 4
  • 3
  • 3
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36958435
how about something like this?

ee.txt
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36958453
is this the same question?

http://www.experts-exchange.com/Database/MySQL/Q_27393290.html

why did you open a new question?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36958457
note, it's possible that a row created for a grouping of

(street_number,
             street_name,
             street_type,
             street_direction,
             unit_number,
             city,
             zip_code)

will be constructed from more than one of the individual tables.

for instance,  the max(id) might be from table2,  max(state) might be from table1 and max(name) might be from table3.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 36958471
this is clearly related, but is distinct.  In this question the rows are consolidated by more complex criteria than simple union

UNION will remove duplicates across all columns,  in this question he's looking for duplicates across a subset of columns
0
 

Author Comment

by:pda4me
ID: 36958525
I tried the query and am getting a 1248 - Every derived table must have its own alias

what am I doing wrong?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 36958540
alias the part that is in (),  sorry I should have included that originally


INSERT INTO table4
    SELECT   MAX(id),
             MAX(state),
             MAX(name),
             street_number,
             street_name,
             street_type,
             street_direction,
             unit_number,
             city,
             zip_code
        FROM (SELECT * FROM table1
              UNION ALL
              SELECT * FROM table2
              UNION ALL
              SELECT * FROM table3) t
    GROUP BY street_number,
             street_name,
             street_type,
             street_direction,
             unit_number,
             city,
             zip_code
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36958562
ok, we can try to implement row_number() functionality in MySQL which is explained here

http://explainextended.com/2009/03/08/analytic-functions-sum-avg-row_number/
0
 

Author Comment

by:pda4me
ID: 36958617
close... getting a 1136 - Column count doesn't match value count at row 1

I added ID, STATE and NAME to illustrate that there were other columns in the table that I did not wish to compare when inserting records...do I need to include all the columns?

what is the MAX()?
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 36958643
add column names into select parts

INSERT INTO table4(id, state, name,..., zip_code)
    SELECT   MAX(id),
             MAX(state),
             MAX(name),
             street_number,
             street_name,
             street_type,
             street_direction,
             unit_number,
             city,
             zip_code
        FROM (SELECT id, state, name,..., zip_code FROM table1
              UNION ALL
              SELECT id, state, name,..., zip_code FROM table2
              UNION ALL
              SELECT id, state, name,..., zip_code FROM table3) t
    GROUP BY street_number, 
             street_name,
             street_type,
             street_direction,
             unit_number,
             city,
             zip_code

Open in new window

0
 

Author Comment

by:pda4me
ID: 36961491
Almost, I am getting the error

1136-Column Count doesn't match value count at row 1

Here is the full sql query with all the column values, what am I missing?

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

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

850 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