www.webdeveloper.com
Results 1 to 2 of 2

Thread: left join with extra conditions

Hybrid View

  1. #1
    Join Date
    Oct 2011
    Posts
    14

    left join with extra conditions

    I'm trying to setup a rating app for images. I want to display the images that haven't been rated by the current logged user.

    This covers two tables, I've successfully joined the tables but the extra conditions:
    1. must be an image
    2. exclude any images that have already been rated by the current user
    are not working.

    Its displaying the results as if the 2 AND conditions aren't there at all.

    Code:
    SELECT ver_data_media.id, ver_data_media.value, ver_data_media.ref, ver_data_media.type, ver_data_rating.a
    FROM ver_data_media
    LEFT JOIN ver_data_rating
    ON ver_data_media.ref = ver_data_rating.a
    AND ver_data_media.type = 'image'
    AND ver_data_rating.a != $current_user->ID

    Any advice would be greatly appreciated, thanks Frank.

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Code:
    SELECT 
      ver_data_media.id, 
      ver_data_media.value, 
      ver_data_media.ref, 
      ver_data_media.type, 
      ver_data_rating.a
    
    FROM ver_data_media LEFT JOIN ver_data_rating
      ON ver_data_media.ref = ver_data_rating.a
    
    WHERE  ver_data_media.type  = 'image'
      AND ver_data_rating.a    != $current_user->ID
    Assuming MySQL, you could put all three conditions into the FROM clause. I have learned that this is not typically what you want in the case of a two table join. Putting multiple conditions into the JOIN ... ON clause is used when you want to... lets call it "persuade" MySQL to join a certain way. It seems much more appropriate to put the extra two conditionals into the WHERE clause. However, for the sake of showing you how it would be done in the ON clause:

    Code:
    SELECT 
      ver_data_media.id, 
      ver_data_media.value, 
      ver_data_media.ref, 
      ver_data_media.type, 
      ver_data_rating.a
    
    FROM ver_data_media LEFT JOIN ver_data_rating
      ON (ver_data_media.ref = ver_data_rating.a
            AND ver_data_media.type  = 'image'
            AND ver_data_rating.a    != $current_user->ID);
    Additionally, I would not write values from PHP variables directly into the query. Possibly a format string is better? You could use sprintf(%d) to force $current_user->ID to be an integer.

    tl;dr Parenthesis are your friend.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles