Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: Creating query from mapped columns across multiple tables?

    I have 5 tables consisting of property (as in real-estate) data.

    One of these table so happens to pull all the common elements into one table so that I don't have to do this, whoohoo.

    However, a few of these tables have a column field that I need to show in my select statement, but I can't figure out how to make it work.

    This is the easiest way to understand.

    For this example, assume the following tables exist:

    residential
    condo
    multi-family

    Residential and Condominium properties can have a fields called CommunityAmenities and AssociationFee. Multi-Family does not have these fields.

    Now, how would I pull together a single table that includes communityamenities and associationfee when applicable, and Null if not?

    I thought a Left Join would do the trick, but it requires that I specify if communityamenities comes from the residential or condo table.. no good.

    So then I think maybe a left join + a union would do the trick? I can't seem to figure out if I'm doing it right though.

    Code:
    SELECT mls.mlsnum, communityamenities
    FROM mls
    LEFT JOIN multifamily
    ON mls.mlsnum = multifamily.mlsnum
    UNION
    SELECT mlsnum, communityamenities FROM condo
    UNION
    SELECT mlsnum, communityamenities from residential
    I wanted to add one more detailed example of what I'm trying to do:

    Here are all the columns I need extracted. As you can see, most of them are from one table, but a few fields are across multiple tables (the ones prefixed with cnd/res have data in either the CND table or the RES, not both). Those are the ones causing me a headache:

    Code:
    '',
    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,
    '',
    res.totalbedrooms,
    cnd/res.totalfullbaths,
    mls.sqfttotal,
    '',
    mls.acres,
    mls.yearbuilt,
    mls.heatingsystem,
    mls.coolingsystem,
    cnd/res.heatingsource,
    mls.garagedescription,
    mls.garagecapacity,
    mls.zoning,
    '',
    mls.constructiontype,
    mls.roofmaterial,
    res.waterfrontdesc,
    concat(mls.highschool,char(13),mls.juniorhighschool),
    '',
    mls.style,
    cnd/res.associationfee,
    '',
    '',
    '',
    '',
    mls.OfficeCoListOfficeName,
    '',
    cnd/res.CommunityAmenities,
    Last edited by IronTomato; 01-06-13 at 16:17. Reason: Added more examples

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by IronTomato View Post
    For this example, assume the following tables exist:

    residential
    condo
    multi-family
    and then you introduce a fourth table without explanation

    so how about you explain the relationship between the mls table and these three
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    5
    Sorry! I did miss that in my explanation.

    Here is the table structure:

    1. 6 tables exist that have specific property type data (condo, residential, land, rental, multifamily, commercial)
    2. a 7th table exists called MLS that takes all of the common elements from the 6 above tables and puts them into one large table.


    Does this make sense? So essentially pseudo code is something like

    SELECT mls.commonfields, res/cnd/land/multifamily.communityamenities
    From MLS, res, cnd, land, multifamily

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and how are these 7 tables related?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2012
    Posts
    5
    So the MLS table contains data that is in all the other tables. It is essentially a redundant set of data that brings together the common elements.

    The only relational element would be the MLS # or MLSNUM field. A 6-8 digit code that exists for every record.

    So all records exist in the MLS table and then a corresponding row exists within each of the other tables (condo, residential, land, rental, multifamily, commercial).

    Once again, back to the crux of the issue, I need a view the pulls some of the data from these corresponding rows within the other tables (condo, residential, land, rental, multifamily, commercial).

    So the example I was using, CommunityAmenities does not exist in the main MLS table, but DOES exist in the CONDO, RESIDENTIAL, RENTAL, and MULTIFAMILY tables.

    So my view needs to populate the CommunityAmenities field with the data from the corresponding table.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm going to assume that for a given mlsnum, the corresponding ro does ~not~ actually exist within each of the other tables, but rather, in only one of them

    correct?

    so, just use LEFT OUTER JOINs to join all 6 subtype tables to the mls table, and only one of them will have a matching row

    then you could use COALESCE on the 6 related columns to return the non-null value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •