SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql query string construction

    Hi all...

    I am relatively new to mysql. I'm currently working on a real estate web project and have set up the following database table outline:

    properties
    propID
    propName
    propAddress

    languages
    langID
    langName

    propDes (Text description of property)
    propDesID
    propDesText
    propID(from properties table)
    langID(from languages table)

    propAm (List of Property Amenities)
    propAmID
    propAmText
    propID
    langID


    Now what I would like to do -- with one query string -- is to retrieve the following fields:

    propName
    propAddress
    propDesText
    propAmText

    for the a specified langID and propID.

    Any help?

    Thanks a bunch!

  2. #2
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you might have to do two queries of the form:

    SELECT
    properties.propName
    , properties.propAddress
    , propDes.propDesText
    FROM
    properties
    INNER JOIN
    propDes(USING propID)
    WHERE
    properties.propID=x AND propDes.langID=y

    If the list of possible amenties doesn't change, or doesn't change often, you could consider denormalising ie merge the description and amenities tables into one with cols for description, amenity1, amenity2 etc.

    I think you probably know most of the following but just in case: http://www.oreilly.com/catalog/javad...apter/ch02.pdf.

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A good read, another PDF download is about Java Data Objects which is also worthwhile downloading and reading

    http://www.oreilly.com/catalog/jvadtaobj

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you do not need the columns prodDesID or propAmID

    Code:
    select propName
         , propAddress
         , propDesText
         , propAmText
      from properties
    inner
      join propDes 
        on properties.propID
         = propDes.propID     
       and propDes.langID = 42
    inner
      join propAm
        on properties.propID
         = propAm.propID     
       and propAm.langID = 42
     where properties.propID = 937
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you use ON like that to restrict rows in the result set, ie should the langID=42 be in a WHERE clause?

    EDIT: lol I just noticed your byline.. My mistake then, I guess.

  6. #6
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you all for the help!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in this case, yes, you can have the lang condition in the WHERE clause instead, but only because it's an inner join

    if it were a left outer join, the difference makes a difference in what you might get back
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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