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
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
is this the same question?
https://www.experts-exchange.com/questions/27393290/Combine-3-tables-into-a-single-table-mysql-and-PHP.html
why did you open a new question?
https://www.experts-exchange.com/questions/27393290/Combine-3-tables-into-a-single-table-mysql-and-PHP.html
why did you open a new question?
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.
(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
UNION will remove duplicates across all columns, in this question he's looking for duplicates across a subset of columns
ASKER
I tried the query and am getting a 1248 - Every derived table must have its own alias
what am I doing wrong?
what am I doing wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/
http://explainextended.com/2009/03/08/analytic-functions-sum-avg-row_number/
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()?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
ee.txt