SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Php_penguin's Avatar
    Join Date
    Aug 2004
    Location
    Colwyn Bay, Wales, UK
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    3 related tables in one query?

    Hi there, I want have these 3 tables:
    - Properties : contains a list of properties
    - Images : contains images (the urls of)
    - Attributes : contains a key=>value pairing for properties, any number of per.

    What i need to do is:
    Get all the properties in the database
    get the Image relating to the property, where the Image:ID = Property:mainimg
    get ALL attributes relating to the property, where the Attrib:Property = Property:ID

    how would i do this?

    I am aware of how to join two tables, but what about with 3?

  2. #2
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just write multiple joins

    Code:
    SELECT *
      FROM properties
      LEFT OUTER
      JOIN image
        ON image.ID = property.mainimg
      LEFT OUTER
      JOIN attrib
        ON attrib.property = property.ID
    Once thing to remember is that you will have a row for each possible combination of the rows returned. Which means that if you have multiple images per property, you'll get multiple rows.
    MySQL v5.1.58
    PHP v5.3.6

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    brandon is right, you will not get what you're expecting

    if a given property has N images and M attributes, the 3-table join will have N*M result rows -- likely not what you want

    return the two one-to-many relationships separately with two queries
    rudy.ca | @rudydotca
    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
  •