SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    JOINs and Missing Records

    I tried to Google this and to search the forums here but could not find what I need. Basically there are several instances where I am pulling data from two tables together. In one case I have entries with a UserID field and I am pulling the Username from another table linked on this field. The problem I have is when no corresponding entry exists in the second table, the record does not show up at all.

    Sample tables and data, SQL, and records returned are below:

    UserPosts:
    P_ID, P_UserID, P_Comment
    1, 1, 'Test 1'
    2, 2, 'Test 2'
    3, 7, 'Test 3'

    Users:
    U_ID, U_Username
    1, 'Test User 1'
    2, 'Test User 2'
    3, 'Test User 3'
    10, 'Test User 10'

    SQL: SELECT P_ID, P_UserID, P_Comment, U_Username FROM UserPosts, Users WHERE (UserPosts.P_UserID = Users.U_ID)

    Returns:
    1, 1, 'Test 1', 'Test User 1'
    2, 2, 'Test 2', 'Test User 2'

    How can I solve this problem? Do I just require that the data not be deleted so that related queries function properly?

    Thanks,

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Wes...you're "problem" is really just an inner join properly doing what it was designed to do...only records that exists in both tables will return. Since there is no U_ID = 7 in the Users table, no record returns for that P_UserID from the UserPosts table. Use an outer join and you'll get all the records you want.
    Code:
    select  P_ID,
            P_UserID,
            P_Comment,
            U_Username
    from    UserPosts UP
    left    outer join Users U on UP.P_UserID = U.U_ID
    ...when no match on the user ID occurs, NULL will be shown.
    Last edited by null; May 1, 2004 at 11:45.

  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)
    your problem is actually a bit deeper

    how the heck could you have a post from a user that doesn't exist???

    the answer is: no relational integrity

    mysql myisam tables don't provide support for relational integrity, in this example ON DELETE CASCADE, so when you delete a user from the users table, you must also ensure, with application scripting logic, to delete all related rows in all related tables yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    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)
    p.s. null, your query needs a wee correction

    from UserPosts UP
    left outer join Users U on UP.P_UserID = U.U_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did try and find more about different types of joins but never found that one of them would handle what I need. Thanks null for pointing me to outer joins.

    r937: The posts by deleted users are not removed because they will be in a comment system and I want them to stay even if the user no longer exists. In another situation, I have old entries that could be referenced in the future and they should also be accessible whether the user exists or not.

  6. #6
    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)
    when you display a post by a user who no longer exists, what do you display as the user name? and if you use the same logic as for normal posts, how did you manage to do it without knowing about outer joins? don't tell me you did two queries, one to find the post and a second to find the user if any
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    when you display a post by a user who no longer exists, what do you display as the user name? and if you use the same logic as for normal posts, how did you manage to do it without knowing about outer joins? don't tell me you did two queries, one to find the post and a second to find the user if any
    Well, the system is not fully in place - when I tried the other way it wasn't working the way I wanted because of the records not showing up. The comment system is for a link, so I am selecting records by LinkID and I wanted to display the user by something other than their UserID. For entries without usernames, I can check for null and then list the username as "Unknown" or something to that affect.

    My other situation is very similar. I need to be able to retrieve old records and matching user details whether the user still exists or not. If a user does not exist, I can just use "Unknown" like above.

    Don't worry, I did not try using two queries to load up two sets of data and match it together in my code. .

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahh, yes Rudy...thx for the catch.

    Btw, historic data is one of the main reasons why I'll see "orphan" records such as the posters. It may be a violation of text book relational integrity, but it does serve a purpose. Depending on the application and DB setup, just b/c someone is deleted from the DB doesn't necessarily mean you should lose 1000's of records of their data b/c they're no longer related.

    There are better work arounds for a situation like this that I'd like to suggest though...

    1) create a "status" field, or something similar, that would note the users status. If they're still current, deleted, etc. Their record would still be in the User table so you'd always have a name and ID to relate to, but the status would let you know they've been "deleted". Or...

    2) Create a "Deleted" table that would dump these people's records into another table, but why another table if you could just add a field to the User table?

    Just suggestions.

  9. #9
    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)
    okay, wes, understood

    do yourself a favour, don't code for the missing username in your script, do this and it should be transparent to you:
    Code:
    select P_ID
         , P_UserID
         , P_Comment
         , coalesce(U_Username,'*missing*') as username
      from UserPosts UP
    left outer 
      join Users U 
        on UP.P_UserID 
         = U.U_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks to null and rudy. In one case I will probably use null's idea of a status field because it will be more appropriate. For the comment system I will go with rudy's suggestion.


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
  •