views:

46

answers:

4

hi, 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

alt text

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
+4  A: 

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
Functions in joins should be avoided. Very poor performers.
HLGEM
+2  A: 

@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
bobs
A: 

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

jay
A: 

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
Joe Stefanelli