Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Sep 2006
    Location
    Colorado
    Posts
    132
    Thanks
    7
    Thanked 1 Time in 1 Post

    Left Join not working correctly

    Here is my table structure:
    Table: User
    Fields: UserId,FirstName,LastName,FriendCount

    Table: Network_User
    Fields: UserId,NetworkId

    Table: Network
    Fields: NetworkId, Network

    Table: Keyword_User
    Fields: UserId,KeywordId

    Table: Keyword
    Fields: KeywordId, Keyword

    This query works correctly:
    SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
    LEFT JOIN `Network_User` nu ON nu.UserId = u.UserId
    LEFT JOIN `Network` n ON nu.NetworkId = n.NetworkId
    WHERE n.Network = 'Purdue' ORDER BY u.FriendCount DESC
    It returns 2 rows.

    This one does not:
    SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
    LEFT JOIN `Keyword_User` ku ON ku.UserId = u.UserId
    LEFT JOIN `Keyword` k ON ku.KeywordId = k.KeywordId
    WHERE k.Keyword = 'weeds' ORDER BY u.FriendCount DESC
    It returns an empty set, where it should return 2 rows like the other one.

    I know the correct data is in the tables for it to return.

    I noticed that
    SELECT * FROM `Keyword` WHERE `Keyword` = 'weeds'
    Also fails.

    Any idea why?
    -bubbles

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Humor me.

    Try this query:
    Code:
    SELECT * FROM `Keyword` WHERE `Keyword` LIKE '%weed%'
    I'm trying to see if perhaps you don't *REALLY* have any records with keyword "weed". As in, maybe there is a space in the value???

    Heck, for that matter, try
    Code:
    SELECT * FROM `Keyword` WHERE `Keyword` LIKE '%ee%'
    Or most anything that is likely to match *SOME* records.

  • #3
    Regular Coder
    Join Date
    Sep 2006
    Location
    Colorado
    Posts
    132
    Thanks
    7
    Thanked 1 Time in 1 Post
    No I've looked at the rows in phpMyAdmin. There are two rows in Keyword_User with the user ids and the keyword id of 'weeds' which is in the Keyword table.
    -bubbles

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Oh, stupid me!

    This is one of my most popular "rants"!!!

    When you use a WHERE clause to limit records from a *DEPENDENT* table in an outer join (that is, the right-side table in a LEFT join...the left side is the independent table), you just converted that OUTER join to an INNER join!!!!!!!!!

    Look here to see why:
    http://www.codingforums.com/showthre...192#post818192

    SO...actually, BOTH your queries are wrong. I just got misled when you said the one worked. You just got lucky in the first case.

    Try these:
    Code:
    SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
    LEFT JOIN `Network_User` nu ON nu.UserId = u.UserId
    LEFT JOIN `Network` n ON ( nu.NetworkId = n.NetworkId AND n.Network = 'Purdue' )
    ORDER BY u.FriendCount DESC
    
    SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
    LEFT JOIN `Keyword_User` ku ON ku.UserId = u.UserId
    LEFT JOIN `Keyword` k ON ( ku.KeywordId = k.KeywordId AND k.Keyword = 'weeds' )
    ORDER BY u.FriendCount DESC
    SORRY!

    (The parens in the ON condition aren't needed in MySQL but are in some DBs. But I like to put them in, regardless, because it clarifies the intent of the join.)

  • #5
    Regular Coder
    Join Date
    Sep 2006
    Location
    Colorado
    Posts
    132
    Thanks
    7
    Thanked 1 Time in 1 Post
    That Keyword query spits out 115, the same as the number of rows in the Keyword table. Sorry for my newbness, I'm horrible with joins.

    I'm trying to select ONLY the users who have the 'weeds' keyword linked to their profile in the Keyword_User table.
    -bubbles

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Then you do *NOT* want a LEFT JOIN.

    The definition of a LEFT JOIN is "get *ALL* records from the table on the left side of the JOIN and then get matching records from the right side table *IF THEY EXIST*...but even if there are not matching right side records, still get the all the left side records."

    If you want to limit the records in the left side table, use INNER JOINs instead.

    However....

    Given that your misuse of the WHERE clause should have changed your LEFT JOIN *into* an INNER JOIN, I'm not sure what is going to happen.

    But give it a shot:
    Code:
    SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
    INNER JOIN `Keyword_User` ku ON ku.UserId = u.UserId
    INNER JOIN `Keyword` k ON ( ku.KeywordId = k.KeywordId AND k.Keyword = 'weeds' )
    ORDER BY u.FriendCount DESC
    You know, I don't think this is going to possibly work.

    You said that just doing
    Code:
    SELECT * FROM `Keyword` WHERE `Keyword` = 'weeds'
    returns no records, right? So you have a problem in that table and I have no idea why.

  • Users who have thanked Old Pedant for this post:

    bubbles19518 (06-11-2009)


  •  

    Posting Permissions

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