Link to home
Start Free TrialLog in
Avatar of pda4me
pda4me

asked on

MySQL query combine 3 tables into 1

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
Avatar of Sean Stuber
Sean Stuber

how about something like this?

ee.txt
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.

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
Avatar of pda4me

ASKER

I tried the query and am getting a 1248 - Every derived table must have its own alias

what am I doing wrong?
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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/
Avatar of pda4me

ASKER

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()?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pda4me

ASKER

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