SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Adding a condition to a JOIN?

    Hi,

    I have 4 tables holding data on houses. Using a join in the query is straight forward enough for three of the tables.

    Code:
    SELECT 
    jos_hp_properties.id AS prop_id, 
    jos_hp_properties.name AS prop_name, 
    jos_hp_prop_types.name AS prop_type,
    price, intro_text, xpos, ypos, thumb
     
    FROM jos_hp_properties
    
    JOIN 
        jos_hp_prop_types 
    ON 
        jos_hp_prop_types.id=jos_hp_properties.type
    
    JOIN 
        jos_hp_photos 
    ON 
        jos_hp_photos.property=jos_hp_properties.id
    
     WHERE agent=$dev_id
    However, the fourth table 'jos_hp_properties2' contains multiple entries for each house. E.g. 'Type of heating', 'no. of rooms' and 'parking space'

    Constructed as;
    id | property | field | value

    I would like to extract the 'no. of rooms' which I know has a 'field' of 18.

    Am I close with?
    Code:
    SELECT 
    jos_hp_properties.id AS prop_id, 
    jos_hp_properties.name AS prop_name, 
    jos_hp_prop_types.name AS prop_type,
    jos_hp_properties2.value AS prop_value,
    price, intro_text, xpos, ypos, thumb
     
    FROM jos_hp_properties
    
    JOIN 
        jos_hp_properties2 
    ON 
        jos_hp_properties2.property=jos_hp_properties.id (WHERE field = 18)
    
    JOIN 
        jos_hp_prop_types 
    ON 
        jos_hp_prop_types.id=jos_hp_properties.type
    
    JOIN 
        jos_hp_photos 
    ON 
        jos_hp_photos.property=jos_hp_properties.id
    
     WHERE agent=$dev_id
    I'd appreciate any guidance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    FROM jos_hp_properties
    
    JOIN 
        jos_hp_properties2 
    ON 
        jos_hp_properties2.property=jos_hp_properties.id 
    AND jos_hp_properties2.field = 18
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's brilliant! Thank you so much. Another command learnt!

  4. #4
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok - sorry to be a pain but if Im using a join does that mean there has to be some data in that table?

    Some of my properties do not have photos and therefore nothing in the jos_hp_photos table. These ones seem to be being left out of the result. Is this because of the JOIN?

    Code:
    JOIN 
        jos_hp_photos 
    ON 
        jos_hp_photos.property=jos_hp_properties.id

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, if some properties don't have photos, then you must use a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's great! Thank you again.

  7. #7
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm. I've just noticed that if a property has more than 1 photo it is returned the same number of times that it has pictures.

    E.g 3 pictures = That property 3 times in the results.

    Is there a way to prevent this? Or to remove the repeated results afterward?

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Do you need the photos table? Do you select any value from that table?
    If the answer is no, eliminate it from your query, and the problem will disappear
    If the answer is yes, then you'll have to use PHP (or whatever language you use) to loop through the result and handle the duplicate values.

  9. #9
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guido,

    I do need the photos as I want to use the first one from each and ignore the others.

    Can I use array_unique to do as you suggest? I am currently using the following to output the data.

    PHP Code:
    $result mysql_query($query);
    $result mysql_query($query);
    if (!
    $result) {
      die(
    "Invalid query: " mysql_error());
    }
    while(
    $row mysql_fetch_array($result)){
    echo 
    '<strong>'.$row['prop_name'].'</strong>'///Etc. 

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you only want the first photo for each property, then I'm sure there's a way to do it with MySQL.
    What does 'first' mean? The photo with the lowest id?

  11. #11
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for idea. I imagined that the lowest id would work until the first or primary image was changed. However, the jos_hp_photos table does have a 'order' column. I'll try using this.

    Code:
     `id` int(11) NOT NULL auto_increment,
      `property` int(11) NOT NULL default '0',
      `ordering` int(11) NOT NULL default '0',

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT jos_hp_properties.id AS prop_id
         , jos_hp_properties.name AS prop_name
         , jos_hp_prop_types.name AS prop_type
         , jos_hp_properties2.value AS prop_value
         , price
         , intro_text
         , xpos
         , ypos
         , thumb
      FROM jos_hp_properties
    INNER
      JOIN jos_hp_properties2 
        ON jos_hp_properties2.property = jos_hp_properties.id 
       AND jos_hp_properties2.field = 18
    INNER
      JOIN jos_hp_prop_types 
        ON jos_hp_prop_types.id=jos_hp_properties.type
    LEFT OUTER
      JOIN ( SELECT property
                  , MIN(ordering) AS first_ordering
               FROM jos_hp_photos
             GROUP
                 BY property ) AS m
        ON m.property = jos_hp_properties.id
    LEFT OUTER
      JOIN jos_hp_photos 
        ON jos_hp_photos.property = jos_hp_properties.id
       AND jos_hp_photos.ordering = m.first_ordering
     WHERE agent = $dev_id
    joins for photos are LEFT OUTER in case the property has no photos
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict jamus's Avatar
    Join Date
    Jul 2004
    Location
    Devon, UK
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Im struggling. I've included the vlaue for the photo order value but Im not sure how to apply it.

    Code:
    	 SELECT 
    jos_hp_properties.id AS prop_id, 
    jos_hp_properties.name AS prop_name, 
    jos_hp_agents.name AS prop_agent,
    jos_hp_properties.plot AS prop_plot,
    jos_hp_prop_types.name AS prop_type,
    jos_hp_properties2.value AS prop_value,
    jos_hp_photos.ordering AS photo_order,
    featured, note, price, situation, intro_text, xpos, ypos, thumb
     
    FROM jos_hp_properties 
    
    JOIN 
       jos_hp_properties2 
    ON 
       jos_hp_properties2.property=jos_hp_properties.id 
    AND jos_hp_properties2.field = 18
    
    JOIN 
        jos_hp_prop_types 
    ON 
        jos_hp_prop_types.id=jos_hp_properties.type
    	
    JOIN
    	jos_hp_agents
    ON
    	jos_hp_agents.id=jos_hp_properties.agent
    
    LEFT OUTER JOIN 
        jos_hp_photos 
    ON 
        jos_hp_photos.property=jos_hp_properties.id
    	
    	 WHERE featured=1 LIMIT 1
    This works but returns results with using secondary photos.

    I need to only get the results with the lowest 'photo_order' so that I can Limit all the results to 1 and then ORDER BY RAND(). Can this be done in MySQL?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    did you see my post just before yours?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jamus View Post
    Constructed as;
    id | property | field | value
    This is known as EAV table type. It is easy to put information in and dreadful to retrieve information from it.

    Read up on Database Normalization and EAV to find out why you'd want to model this data in a different way before you get further.


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
  •