How to display database records even if cell is zero

Question

I am here again to ask about my problem with the presentation I created. There are 8 entries on my tblEvents, but I created a view that displays only 3 entries. I suspect the view is not reading a null value from my tblEvents. How to fix it? 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 

Answer 1

I assume that if the record has not been edited, you want to join 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) 

Answer 2

@RedFilter answer sounds good to me. But if this is not suitable for the connection in the AddedBy column, you can change the connection to an external connection.

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

Answer 3

use LEFT OUTER JOIN

SELECT dbo.tblUsers.UserID, dbo.tblUsers.UserFirstName + 'UserLastName' Posted by AS, dbo.tblUsers.UserLastName, dbo.tblUsers.UserEmailAddress, dbo.tblEvents.EventName, dbo.tblEvents.EventName.bot.boventsbo.ventventbobovent.bovent.bovents .tblEvents.EventVenue, dbo.tblEvents.EventDate, dbo.tblEvents.AddedBy, dbo.tblEvents.Pending, dbo.tblEvents.DateAdded, dbo.tblEvents.DateEditted, dbo.tblUsers.UserName FROM dbo.tbltersbtbluttbluttblter ON dbo.tblEvents.EdittedBy = dbo.tblUsers.UserID

Answer 4

I am going to offer a slightly different solution based on a slightly different assumption. Assuming you want to return users and all events that they 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