SELECT * FROM ((SELECT '' acreage, comAddress address1, comAgent agentName, comArea area, '' bathroom1, '' bathroom2, '' bathroomCount, '' bedroom1, '' bedroom1_floor, '' bedroom1_level, '' bedroom1_size, '' bedroom2, '' bedroom2_floor, '' bedroom2_level, '' bedroom2_size, '' bedroom3, '' bedroom3_floor, '' bedroom3_level, '' bedroom3_size, '' bedroom4, '' bedroom4_floor, '' bedroom4_level, '' bedroom4_size, '' bedroom5, '' bedroomCount, comCity city, isForeclosed comIsForeclosed, isShortSale comIsShortSale, CONCAT("|",comComType,"|",comComType2,"|",comComType3,"|",comComType4,"|") comType, comCounty county, '' diningArea, '' diningArea_floor, '' diningArea_level, '' diningArea_size, '' diningRoom, '' diningRoom_floor, '' diningRoom_level, '' diningRoom_size, '' familyRoom, '' familyRoom_floor, '' familyRoom_level, '' familyRoom_size, '' farmType, '' frmIsForeclosed, '' frmIsShortSale, '' garageSize, '' garageType, comHasWaterfront hasWaterfront, comType houseType, '' includes, '' isCondo, '' isManu, '' kitchen, '' kitchen_floor, '' kitchen_level, '' kitchen_size, '' lakeRiver, STR_TO_DATE(comLastModified,'%m/%d/%Y') lastUpdate, '' laundaryRoom_floor, '' laundaryRoom_level, '' laundaryRoom_size, '' laundryRoom, comListingOffice listingOffice, (closingDate="" AND closingPrice=0) listProperty, '' livingRoom, '' livingRoom_floor, '' livingRoom_level, '' livingRoom_size, '' lndIsForeclosed, '' lndIsShortSale, CONCAT(comLot_DimL1,'x',comLot_DimW1) lotSize, comMLSNum mlsNum, '' mulIsForeclosed, '' mulIsShortSale, '' netAddress, '' newConstruction, '' numUnits, '' office, '' office_floor, '' office_level, '' office_size, '' openHouse_date, '' openHouse_time, '' otherRoom, '' otherRoom_floor, '' otherRoom_level, '' otherRoom_size, comListPrice price, '' propertyStyle, comComType propType, comPublicComments publicComments, '' resIsForeclosed, '' resIsShortSale, comSchoolDist schoolDist, CONCAT('Above: ', comAboveGrade, ' Below: ', comBelowGrade) squareFootage, comState state, '' status, '' style, comRemarks summary, comTaxYear taxYear, comVirtualTourURL virtualTour, comLakeName waterfront, comYearBuilt yearBuilt, comTaxAmount yearlyTax, comZip zip, comZoning zoning, 'Commercial' propertyType, 'Eau_Claire' mlsSystem FROM tblListingCommercial WHERE comListingOffice LIKE 'C21 Metro%' ) UNION ALL (SELECT frmTotalAcres acreage, frmAddress address1, frmAgent agentName, frmArea area, '' bathroom1, '' bathroom2, '' bathroomCount, '' bedroom1, '' bedroom1_floor, '' bedroom1_level, '' bedroom1_size, '' bedroom2, '' bedroom2_floor, '' bedroom2_level, '' bedroom2_size, '' bedroom3, '' bedroom3_floor, '' bedroom3_level, '' bedroom3_size, '' bedroom4, '' bedroom4_floor, '' bedroom4_level, '' bedroom4_size, '' bedroom5, '' bedroomCount, frmCity city, '' comIsForeclosed, '' comIsShortSale, '' comType, frmCounty county, '' diningArea, '' diningArea_floor, '' diningArea_level, '' diningArea_size, '' diningRoom, '' diningRoom_floor, '' diningRoom_level, '' diningRoom_size, '' familyRoom, '' familyRoom_floor, '' familyRoom_level, '' familyRoom_size, CONCAT("|",frmFrmType,"|",frmFrmType2,"|",frmFrmType3,"|") farmType, isForeclosed frmIsForeclosed, isShortSale frmIsShortSale, '' garageSize, '' garageType, frmHasWaterfront hasWaterfront, frmType houseType, '' includes, '' isCondo, '' isManu, '' kitchen, '' kitchen_floor, '' kitchen_level, '' kitchen_size, '' lakeRiver, STR_TO_DATE(frmLastModified,'%m/%d/%Y') lastUpdate, '' laundaryRoom_floor, '' laundaryRoom_level, '' laundaryRoom_size, '' laundryRoom, frmListingOffice listingOffice, (closingDate="" AND closingPrice=0) listProperty, '' livingRoom, '' livingRoom_floor, '' livingRoom_level, '' livingRoom_size, '' lndIsForeclosed, '' lndIsShortSale, CONCAT(frmLotDim1_L,'x',frmLotDim1_W) lotSize, frmMLSNum mlsNum, '' mulIsForeclosed, '' mulIsShortSale, '' netAddress, '' newConstruction, '' numUnits, '' office, '' office_floor, '' office_level, '' office_size, '' openHouse_date, '' openHouse_time, '' otherRoom, '' otherRoom_floor, '' otherRoom_level, '' otherRoom_size, frmListPrice price, '' propertyStyle, frmFrmType propType, frmPublicComments publicComments, '' resIsForeclosed, '' resIsShortSale, frmSchoolDist schoolDist, CONCAT('Above: ', frmAboveGrade, ' Below: ', frmBelowGrade) squareFootage, frmState state, '' status, '' style, frmRemarks summary, frmTaxYear taxYear, frmVirtualTourURL virtualTour, frmLakeName waterfront, frmYearBuilt yearBuilt, frmTaxAmount yearlyTax, frmZip zip, frmZoning zoning, 'Farm' propertyType, 'Eau_Claire' mlsSystem FROM tblListingFarm WHERE frmListingOffice LIKE 'C21 Metro%' ) UNION ALL (SELECT lndAcres acreage, lndAddress address1, lndAgent agentName, lndArea area, '' bathroom1, '' bathroom2, '' bathroomCount, '' bedroom1, '' bedroom1_floor, '' bedroom1_level, '' bedroom1_size, '' bedroom2, '' bedroom2_floor, '' bedroom2_level, '' bedroom2_size, '' bedroom3, '' bedroom3_floor, '' bedroom3_level, '' bedroom3_size, '' bedroom4, '' bedroom4_floor, '' bedroom4_level, '' bedroom4_size, '' bedroom5, '' bedroomCount, lndCity city, '' comIsForeclosed, '' comIsShortSale, '' comType, lndCounty county, '' diningArea, '' diningArea_floor, '' diningArea_level, '' diningArea_size, '' diningRoom, '' diningRoom_floor, '' diningRoom_level, '' diningRoom_size, '' familyRoom, '' familyRoom_floor, '' familyRoom_level, '' familyRoom_size, '' farmType, '' frmIsForeclosed, '' frmIsShortSale, '' garageSize, '' garageType, lndHasWaterfront hasWaterfront, lndType houseType, '' includes, '' isCondo, '' isManu, '' kitchen, '' kitchen_floor, '' kitchen_level, '' kitchen_size, lndLakeName lakeRiver, STR_TO_DATE(lndLastUpdate,'%m/%d/%Y') lastUpdate, '' laundaryRoom_floor, '' laundaryRoom_level, '' laundaryRoom_size, '' laundryRoom, lndListingOffice listingOffice, (closingDate="" AND closingPrice=0) listProperty, '' livingRoom, '' livingRoom_floor, '' livingRoom_level, '' livingRoom_size, isForeclosed lndIsForeclosed, isShortSale lndIsShortSale, CONCAT(lndLotDim_L1,'x',lndLotDim_W1) lotSize, lndMLSNum mlsNum, '' mulIsForeclosed, '' mulIsShortSale, '' netAddress, '' newConstruction, '' numUnits, '' office, '' office_floor, '' office_level, '' office_size, '' openHouse_date, '' openHouse_time, '' otherRoom, '' otherRoom_floor, '' otherRoom_level, '' otherRoom_size, lndListPrice price, '' propertyStyle, lndLndType propType, lndPublicComments publicComments, '' resIsForeclosed, '' resIsShortSale, lndSchoolDist schoolDist, CONCAT(lndLotDim_L1,'x',lndLotDim_W1) squareFootage, lndState state, '' status, '' style, lndRemarks summary, lndTaxYear taxYear, lndVirtualTourURL virtualTour, lndLakeName waterfront, '' yearBuilt, lndTaxAmount yearlyTax, lndZip zip, '' zoning, 'Land' propertyType, 'Eau_Claire' mlsSystem FROM tblListingLand WHERE lndListingOffice LIKE 'C21 Metro%' ) UNION ALL (SELECT '' acreage, mulAddress address1, mulAgent agentName, mulArea area, '' bathroom1, '' bathroom2, '' bathroomCount, '' bedroom1, '' bedroom1_floor, '' bedroom1_level, '' bedroom1_size, '' bedroom2, '' bedroom2_floor, '' bedroom2_level, '' bedroom2_size, '' bedroom3, '' bedroom3_floor, '' bedroom3_level, '' bedroom3_size, '' bedroom4, '' bedroom4_floor, '' bedroom4_level, '' bedroom4_size, '' bedroom5, '' bedroomCount, mulCity city, '' comIsForeclosed, '' comIsShortSale, '' comType, mulCounty county, '' diningArea, '' diningArea_floor, '' diningArea_level, '' diningArea_size, '' diningRoom, '' diningRoom_floor, '' diningRoom_level, '' diningRoom_size, '' familyRoom, '' familyRoom_floor, '' familyRoom_level, '' familyRoom_size, '' farmType, '' frmIsForeclosed, '' frmIsShortSale, '' garageSize, '' garageType, mulHasWaterfront hasWaterfront, mulType houseType, '' includes, '' isCondo, '' isManu, '' kitchen, '' kitchen_floor, '' kitchen_level, '' kitchen_size, '' lakeRiver, STR_TO_DATE(mulLastUpdate,'%m/%d/%Y') lastUpdate, '' laundaryRoom_floor, '' laundaryRoom_level, '' laundaryRoom_size, '' laundryRoom, mulListingOffice listingOffice, (closingDate="" AND closingPrice=0) listProperty, '' livingRoom, '' livingRoom_floor, '' livingRoom_level, '' livingRoom_size, '' lndIsForeclosed, '' lndIsShortSale, CONCAT(mulLotDim_L1,'x',mulLotDim_W1) lotSize, mulMLSNum mlsNum, isForeclosed mulIsForeclosed, isShortSale mulIsShortSale, '' netAddress, '' newConstruction, mulNumUnits numUnits, '' office, '' office_floor, '' office_level, '' office_size, '' openHouse_date, '' openHouse_time, '' otherRoom, '' otherRoom_floor, '' otherRoom_level, '' otherRoom_size, mulListPrice price, '' propertyStyle, mulMulType propType, mulPublicComments publicComments, '' resIsForeclosed, '' resIsShortSale, mulSchoolDist schoolDist, CONCAT(mulBldgDim_L1,'x',mulBldgDim_W1) squareFootage, mulState state, '' status, '' style, mulRemarks summary, mulTaxYear taxYear, mulVirtualTourURL virtualTour, mulLakeName waterfront, mulYearBuilt yearBuilt, mulTaxAmount yearlyTax, mulZip zip, '' zoning, 'Multi-Family' propertyType, 'Eau_Claire' mlsSystem FROM tblListingMultiFamily WHERE mulListingOffice LIKE 'C21 Metro%' ) UNION ALL (SELECT resAcres acreage, IF(`netAddressDispY/N` = 'Y',resAddress,'') address1, resAgent agentName, resArea area, CONCAT(resBTH1_DimL,'x',resBTH1_DimW, ' Level: ', resBTH1Level) bathroom1, CONCAT(resBTH2_DimL,'x',resBTH2_DimW, ' Level: ', resBTH2Level) bathroom2, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM (resFullBathQty + resHalfBathQty/2))) bathroomCount, CONCAT(resBD1_DimL,'x',resBD1_DimW, ' Level: ', resBD1Level) bedroom1, resBD1Flooring bedroom1_floor, resBD1Level bedroom1_level, CONCAT(resBD1_DimL,' x ',resBD1_DimW) bedroom1_size, CONCAT(resBD2_DimL,'x',resBD2_DimW, ' Level: ', resBD2Level) bedroom2, resBD2Flooring bedroom2_floor, resBD2Level bedroom2_level, CONCAT(resBD2_DimL,' x ',resBD2_DimW) bedroom2_size, CONCAT(resBD3_DimL,'x',resBD3_DimW, ' Level: ', resBD3Level) bedroom3, resBD3Flooring bedroom3_floor, resBD3Level bedroom3_level, CONCAT(resBD3_DimL,' x ',resBD3_DimW) bedroom3_size, CONCAT(resBD4_DimL,'x',resBD4_DimW, ' Level: ', resBD4Level) bedroom4, resBD4Flooring bedroom4_floor, resBD4Level bedroom4_level, CONCAT(resBD4_DimL,' x ',resBD4_DimW) bedroom4_size, '' bedroom5, resBedroomQty bedroomCount, resCity city, '' comIsForeclosed, '' comIsShortSale, '' comType, resCounty county, CONCAT(resDA_DimL,'x',resDA_DimW, ' Level: ', resDALevel) diningArea, resDAFlooring diningArea_floor, resDALevel diningArea_level, CONCAT(resDA_DimL,' x ',resDA_DimW) diningArea_size, CONCAT(resDR_DimL,'x',resDR_DimW, ' Level: ', resDRLevel) diningRoom, resDRFlooring diningRoom_floor, resDRLevel diningRoom_level, CONCAT(resDR_DimL,' x ',resDR_DimW) diningRoom_size, CONCAT(resFR_DimL,'x',resFR_DimW, ' Level: ', resFRLevel) familyRoom, resFRFlooring familyRoom_floor, resFRLevel familyRoom_level, CONCAT(resFR_DimL,' x ',resFR_DimW) familyRoom_size, '' farmType, '' frmIsForeclosed, '' frmIsShortSale, CONCAT(resGarageSize, ' Car ', resGarageType) garageSize, resGarageType garageType, resHasWaterfront hasWaterfront, resType houseType, resIncludes includes, `condoY/N` isCondo, `manufacturedY/N` isManu, CONCAT(resKT_DimL,'x',resKT_DimW, ' Level: ', resKTLevel) kitchen, resKTFlooring kitchen_floor, resKTLevel kitchen_level, CONCAT(resKT_DimL,' x ',resKT_DimW) kitchen_size, resLakeName lakeRiver, STR_TO_DATE(resLastUpdate,'%m/%d/%Y') lastUpdate, resLaundryFlooring laundaryRoom_floor, resLaundryLevel laundaryRoom_level, CONCAT(resLaundry_DimL,' x ',resLaundry_DimW) laundaryRoom_size, CONCAT(resLaundry_DimL,'x',resLaundry_DimW, ' Level: ', resLaundryLevel) laundryRoom, resListingOffice listingOffice, (closingDate="" AND closingPrice=0) listProperty, CONCAT(resLR_DimL,'x',resLR_DimW, ' Level: ', resLRLevel) livingRoom, resLRFlooring livingRoom_floor, resLRLevel livingRoom_level, CONCAT(resLR_DimL,' x ',resLR_DimW) livingRoom_size, '' lndIsForeclosed, '' lndIsShortSale, CONCAT(resLotSize_L1,'x',resLotSize_W1) lotSize, resMLSNum mlsNum, '' mulIsForeclosed, '' mulIsShortSale, `netAddressDispY/N` netAddress, resNewConst newConstruction, '' numUnits, CONCAT(resOffice_DimL,'x',resOffice_DimW, ' Level: ', resOfficeLevel) office, resOfficeFlooring office_floor, resOfficeLevel office_level, CONCAT(resOffice_DimL,' x ',resOffice_DimW) office_size, STR_TO_DATE(openHouseDate,'%m/%d/%Y') openHouse_date, openHouseTime openHouse_time, CONCAT(resOther_DimL,'x',resOther_DimW,' Level: ', resOtherLevel) otherRoom, resOtherFlooring otherRoom_floor, resOtherLevel otherRoom_level, CONCAT(resOther_DimL,' x ',resOther_DimW) otherRoom_size, resListPrice price, resstyle propertyStyle, resResType propType, resPublicComments publicComments, isForeclosed resIsForeclosed, isShortSale resIsShortSale, resSchoolDist schoolDist, CONCAT('Above: ', resAboveGrade, ' Below: ', resBelowGrade) squareFootage, resState state, resStatus status, resStyle style, CONCAT(resRemarks,resRemarks2) summary, resTaxYear taxYear, resVirtualTourURL virtualTour, resLakeName waterfront, resYearBuilt yearBuilt, resTaxAmount yearlyTax, resZip zip, '' zoning, 'Single-Family' propertyType, 'Eau_Claire' mlsSystem FROM (tblListingResidential LEFT JOIN openHouses ON resMLSNum=mlsNum) WHERE resListingOffice LIKE 'C21 Metro%' ) ) MasterList WHERE listProperty=1 ORDER BY price DESC