sql question

[kleinma]

ugh.. i can't remember this.. but when you do a join in an sql satatement and if you don't get a match on the join field how do you display a different value in its place?? so I have 2 tables.. tblCoaching and tblEmployees

so i have a stamement like this

SELECT tblCoaching.RecordID, tblCoaching.UserID, tblCoaching.EntererID, tblEmployees.NameLastFirst
FROM tblCoaching LEFT JOIN tblEmployees ON tblCoaching.EntererID = tblEmployees.UserId

but if the user was deleted from tblEmployees I want to display just the userid from the coaching table... how do i do this again?? i thoguht it was with a null check or something

[Leather]

What DB type is it? (SQL server/ Access)

[techgnome]

It doesn't matter the SQL type.....\
Matt, this is what you are looking for:

SELECT tblCoaching.RecordID, tblCoaching.UserID, tblCoaching.EntererID, tblEmployees.NameLastFirst
FROM tblCoaching LEFT JOIN tblEmployees ON tblCoaching.EntererID = tblEmployees.UserId
WHERE tblEmployees.UserID IS NULL

[McGenius]

This might be what you're looking for:

SELECT tc.RecordID, tc.UserID, tc.EntererID, tc.NameLastFirst
FROM tblCoaching tc, tblEmployees te
WHERE tc.EntererID = te.UserId

[Leather]

Yes it does matter because to display a value in place of a NULL you are going to have to do some kind of replace, and the way this is done varies depending on the DB....

Your joins don't help as he has already got this far...

[techgnome]

D'OH! I misread what he asked..... OK, I don't know the answer for Access, but in SQL Server, use ISNULL(FieldName, Value to use then FieldName is NULL)

[kleinma]

its sql server 2k

[kleinma]

Originally posted by techgnome
D'OH! I misread what he asked..... OK, I don't know the answer for Access, but in SQL Server, use ISNULL(FieldName, Value to use then FieldName is NULL)

thanks.. i think that is what i am looking for

[kleinma]

yup got it working now... i thought it was isnull.. but my brain was fused to VB for a second and I forgot that you send a second parameter to replace the null value