title: Dealing with ambiguous column names with MYSQL
body: <p>I'm going to do my best to layout exactly what I'm trying to do. I'm working with a real-estate database that has 5 tables related to different types of properties (residential, commercial, condo, land, etc). The database has one table called MLS that brings together all the common elements into one table across these tables. I need a view into these tables that includes mostly data from this common MLS table, but also includes some specific data from a few of the other tables.</p>
  
  <p>I'm going to give an example that includes on field that exists in either the RESIDENTIAL and CONDO tables, but not the others.</p>
  
  <p>In pseudo code</p>
  
  <p>SELECT MLSNUMBER, (residential.CommunityAmenities OR condo.CommunityAmenities)
  FROM MLSTABLE, RESIDENTIALTABLE, CONDOTABLE</p>
  
  <p>Here is the actual code I'm trying, but it gives the ambiguous column name error because CommunityAmenities (and other fields) exist in multiple tables.</p>
  
  <pre><code>USE lsh_retsdata;
  
  SELECT
      '',
      mls.mlsnum,
      mls.propertyclassid,
      '',
      mls.streetnumber,
      mls.streetname,
      '',
      mls.areaid,
      mls.streetletter,
      '',
      mls.StreetAddressDisplay,
      mls.remarks,
      mls.remarks,
      mls.city,
      mls.STATE,
      '',
      mls.zipcode,
      mls.countyid,
      "US",
      mls.latitude,
      mls.longitude,
      mls.listprice,
      '',
      '',
      mls.taxamount,
      '',
      totalbedrooms,
      totalfullbaths,
      mls.sqfttotal,
      '',
      mls.acres,
      mls.yearbuilt,
      mls.heatingsystem,
      mls.coolingsystem,
      heatingsource,
      mls.garagedescription,
      mls.garagecapacity,
      mls.zoning,
      '',
      mls.constructiontype,
      mls.roofmaterial,
      waterfrontdesc,
      CONCAT(mls.highschool,CHAR(13),mls.juniorhighschool),
      '',
      mls.style,
      associationfee,
      '',
      '',
      '',
      '',
      OfficeCoListOfficeName,
      '',
      CommunityAmenities
  FROM rets_property_mls mls
  LEFT JOIN rets_property_mul mul
      ON mls.mlsnum = mul.mlsnum
  LEFT JOIN rets_property_auc auc
      ON mls.mlsnum = auc.mlsnum
  LEFT JOIN rets_property_com com
      ON mls.mlsnum = com.mlsnum
  LEFT JOIN rets_property_llf llf
      ON mls.mlsnum = llf.mlsnum
  LEFT JOIN rets_property_cnd cnd
      ON mls.mlsnum = cnd.mlsnum
  </code></pre>