how to display database records even when cell is null

i'm here again to ask help regarding my problem with the view that i created. On my tblEvents there 8 records but i created the view it only displays 3 records. I'am suspecting that the view doesn't read a null value from my tblEvents. How do i fix this?alt开发者_运维问答 text http://img69.imageshack.us/img69/143/vwevents.png

alt text http://img43.imageshack.us/img43/8418/tblevents.png

SELECT dbo.tblUsers.UserID, 
           dbo.tblUsers.UserFirstName + ' UserLastName' AS Author, 
           dbo.tblUsers.UserLastName, 
           dbo.tblUsers.UserEmailAddress,
           dbo.tblEvents.EventID,
           dbo.tblEvents.EventName, 
           dbo.tblEvents.EventDescription, 
           dbo.tblEvents.EventVenue,
           dbo.tblEvents.EventDate, 
           dbo.tblEvents.AddedBy, 
           dbo.tblEvents.Pending, 
           dbo.tblEvents.DateAdded, 
           dbo.tblEvents.DateEditted, 
           dbo.tblUsers.UserName
  FROM dbo.tblUsers 
    JOIN dbo.tblEvents ON dbo.tblUsers.UserID = dbo.tblEvents.EdittedBy

---

I am assuming that if the record has not been edited, then you want to join on the AddedBy column. See below:

SELECT 
    u.UserID, 
    u.UserFirstName + ' UserLastName' AS Author, 
    u.UserLastName, 
    u.UserEmailAddress, 
    e.EventID,
    e.EventName, 
    e.EventDescription, 
    e.EventVenue, 
    e.EventDate, 
    e.AddedBy, 
    e.Pending, 
    e.DateAdded, 
    e.DateEditted, 
    u.UserName
FROM dbo.tblUsers u
INNER JOIN dbo.tblEvents e ON u.UserID = ISNULL(e.EdittedBy, e.AddedBy)

---

@RedFilter's answer sounds good to me. But, if it's not appropriate to join on the AddedBy column, then you can change the join to an outer join.

LEFT OUTER JOIN dbo.tblEvents ON dbo.tblUsers.UserID = dbo.tblEvents.EdittedBy

---

use LEFT OUTER JOIN

SELECT dbo.tblUsers.UserID, dbo.tblUsers.UserFirstName + ' UserLastName' AS Author, dbo.tblUsers.UserLastName, dbo.tblUsers.UserEmailAddress, dbo.tblEvents.EventID, dbo.tblEvents.EventName, dbo.tblEvents.EventDescription, dbo.tblEvents.EventVenue, dbo.tblEvents.EventDate, dbo.tblEvents.AddedBy, dbo.tblEvents.Pending, dbo.tblEvents.DateAdded, dbo.tblEvents.DateEditted, dbo.tblUsers.UserName FROM dbo.tblEvents LEFT OUTER JOIN dbo.tblUsers ON dbo.tblEvents.EdittedBy= dbo.tblUsers.UserID

---

I'm going to propose a slightly different solution based on a slightly different assumption. Assuming that you want to return users and all the events that they have either added or edited:

SELECT dbo.tblUsers.UserID, 
               dbo.tblUsers.UserFirstName + ' UserLastName' AS Author, 
               dbo.tblUsers.UserLastName, 
               dbo.tblUsers.UserEmailAddress,
               dbo.tblEvents.EventID,
               dbo.tblEvents.EventName, 
               dbo.tblEvents.EventDescription, 
               dbo.tblEvents.EventVenue,
               dbo.tblEvents.EventDate, 
               dbo.tblEvents.AddedBy, 
               dbo.tblEvents.Pending, 
               dbo.tblEvents.DateAdded, 
               dbo.tblEvents.DateEditted, 
               dbo.tblUsers.UserName
      FROM dbo.tblUsers 
        JOIN dbo.tblEvents ON dbo.tblUsers.UserID = dbo.tblEvents.AddedBy
    UNION
    SELECT dbo.tblUsers.UserID, 
               dbo.tblUsers.UserFirstName + ' UserLastName' AS Author, 
               dbo.tblUsers.UserLastName, 
               dbo.tblUsers.UserEmailAddress,
               dbo.tblEvents.EventID,
               dbo.tblEvents.EventName, 
               dbo.tblEvents.EventDescription, 
               dbo.tblEvents.EventVenue,
               dbo.tblEvents.EventDate, 
               dbo.tblEvents.AddedBy, 
               dbo.tblEvents.Pending, 
               dbo.tblEvents.DateAdded, 
               dbo.tblEvents.DateEditted, 
               dbo.tblUsers.UserName
      FROM dbo.tblUsers 
        JOIN dbo.tblEvents ON dbo.tblUsers.UserID = dbo.tblEvents.EdittedBy