SQL is only reading 1 UserID When I'm Requesting A Picture + Name from the ID. Wondering Why

Question

I have an asp repeater reading a datasource and it's linked eventually to this code through the code behind

[dbo].[GetFeatStatic]
AS
BEGIN

   DECLARE @FeaturedStatic TABLE (
    UserID INT,
    UserTitle varchar(50),
    Title varchar(50),
    Summary text,
        DatePosted date,
    FirstName varchar(50),
        LastName varchar(50),
    Picture varchar(100)
   )

   INSERT INTO @FeaturedStatic
       SELECT TOP 6 StaticContent.UserID, StaticContent.UserTitle, StaticContent.Title, SUBSTRING(StaticContent.Article, 0, 200) AS Summary, StaticContent.DatePosted, 'FirstName', 'LastName', 'Picture'
       FROM StaticContent
       INNER JOIN FeaturedStatic ON FeaturedStatic.ContentID = StaticContent.ContentID
       ORDER BY FeaturedStatic.DateFeatured DESC

       UPDATE @FeaturedStatic
       SET
       FirstName = Users.FirstName,
       LastName = Users.LastName,
       Picture = Users.Picture
       FROM Users
       INNER JOIN FeaturedStatic ON UserID = Users.UserID

   SELECT * FROM @FeaturedStatic
END

Wondering why it won't read Users.Picture + Users.First/LastName. I think it has something to do with INNER JOIN FeaturedStatic ON UserID = Users.UserID, but not sure.

Thanks in advance.

Answer 1

You should be able to do this all on your insert without needing the update.

   INSERT INTO @FeaturedStatic
       SELECT TOP 6 StaticContent.UserID, StaticContent.UserTitle, StaticContent.Title, SUBSTRING(StaticContent.Article, 0, 200) AS Summary, StaticContent.DatePosted, Users.FirstName, Users.LastName, Users.Picture
       FROM StaticContent
       INNER JOIN FeaturedStatic 
           ON FeaturedStatic.ContentID = StaticContent.ContentID
       INNER JOIN Users
           ON StaticContent.UserID = Users.UserID
       ORDER BY FeaturedStatic.DateFeatured DESC

Answer 2

I don't see that you need the table variable, you can do it all in one single SELECT which will be more performant:

SELECT TOP 6 StaticContent.UserID, StaticContent.UserTitle, StaticContent.Title, SUBSTRING(StaticContent.Article, 0, 200) AS Summary, StaticContent.DatePosted, Users.FirstName, Users.LastName, Users.Picture
FROM StaticContent
    INNER JOIN FeaturedStatic ON FeaturedStatic.ContentID = StaticContent.ContentID
    INNER JOIN Users ON FeaturedStatic.UserID = Users.UserID
ORDER BY FeaturedStatic.DateFeatured DESC

Answer 3

It looks to me like your query can be simplified like so:

SELECT TOP 6 SC.UserID, SC.UserTitle, SC.Title, SUBSTRING(SC.Article, 0, 200) AS Summary, SC.DatePosted, U.FirstName, U.LastName, U.Picture
FROM StaticContent SC
INNER JOIN FeaturedStatic FS ON FS.ContentID = SC.ContentID
INNER JOIN Users U ON SC.UserID = U.UserID
ORDER BY FS.DateFeatured DESC

As to your missing fields problem, I'd imagine that your repeater control is configured to only look for the columns that you see.