SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with LEFT JOIN

    I have a query that runs fine as

    Code:
    select t1.postid, t1.forumid, l.postid, l.liketype as lpost from posts as t1 LEFT JOIN postlikes AS l ON t1.postid = l.postid
    All the rows from t1 come back regardless of whether there are corresponding rows in table l.

    But when I put a where condition on the l table, all I get back are rows from t1 that have corresponding records in l

    Code:
    select t1.postid, t1.forumid, l.postid, l.liketype as lpost from posts as t1 LEFT JOIN postlikes AS l ON t1.postid = l.postid where l.liketype = 'L'
    I thought that was the way LEFT JOIN worked? How can I restrict the rows from table l and still get all the rows from t1?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    first thing i want to teach you is to stop writing your sql all on one humoungously long single line

    man, i ~hate~ scrolling sideways

    so this time, i'll do it for you --
    Code:
    SELECT t1.postid
         , t1.forumid
         , l.postid
         , l.liketype as lpost 
      FROM posts as t1 
    LEFT OUTER
      JOIN postlikes AS l 
        ON l.postid = t1.postid
     WHERE l.liketype = 'L'
    okay, that's your query, and now i'm going to make a very small change --
    Code:
    SELECT t1.postid
         , t1.forumid
         , l.postid
         , l.liketype as lpost 
      FROM posts as t1 
    LEFT OUTER
      JOIN postlikes AS l 
        ON l.postid = t1.postid
       AND l.liketype = 'L'
    can you spot the difference?

    see also this recent thread -- http://www.sitepoint.com/forums/mysq...oe-756631.html

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that. Hopefully there won't be a next time.

    And yes I see the difference and it works fine. And I should have gone back to the book first but I went out to the MySQL site. In your book, you use LEFT OUTER JOIN but on the MySQL site they use LEFT JOIN. Is there a left join or does it behave just like an inner join?

  4. #4
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhhhhhhhhh, I see. You are attaching the 'L' criteria to the join. Okay, got it.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the word OUTER is optional. You've caught on, but to clarify, if you have a restriction on the data from the right hand table of a LEFT JOIN, that restriction belongs in the join clause. Putting it in a WHERE clause, it gets applied after the join and thus filters out non matching rows from the LEFT HAND (or first listed) table.

  6. #6
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the input. And I had read "around" this topic while reading the MySQL docs but couldn't get the syntax. So once I saw it it made perfect sense. Very valuable lesson to learn. I'm getting there.


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
  •