JOINs and Missing Records

[wdmny]

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,

[null]

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.

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.

[r937]

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

[r937]

p.s. null, your query needs a wee correction

from UserPosts UP
left outer join Users U on UP.P_UserID = U.U_ID

[wdmny]

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.

[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

[wdmny]

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. .

[null]

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.

[r937]

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:

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

[wdmny]

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.