CODEWITHSUNDEEP
sqlstored-proceduresinner-joinwhere-clause
mitchellbutler
mitchellbutler

Reputation: 63

SELECT command with a WHERE condition and an INNER JOIN

I'm sure I must be making a trivial mistake here, but I've been searching around for help with this problem and all I can find is information on conditional INNER JOINs.

< EDIT > The problem is that this stored procedure is not returning anything at all. If I type just:

SELECT TOP (6) UserID, Category, Title, SUBSTRING(Article, 0, 200) AS Summary, DatePosted
FROM ContribContent
WHERE (DateFeatured IS NOT NULL)
ORDER BY DateFeatured DESC

Into the console then I get values returned. So it must be something to do with the inner-join? < / EDIT >

The idea is to:

  1. take the content which has been featured (DateFeatured is NOT NULL) and place it all into a temporary table
  2. get the user names and picture from the users table and match them to the values in the temporary table using the UserID value.
  3. sort the temporary table in order of the date each post was featured.
  4. select the top six entries from the table

Here's the code:

ALTER PROCEDURE [dbo].[admin_GetFeaturedContrib]
AS
BEGIN

DECLARE @FeaturedContrib TABLE (
 UserID INT,
 Category INT,
 Title varchar(100),
 Summary varchar(200),
 DatePosted date,
 FirstName varchar(50),
 LastName varchar(50),
 Picture varchar(100)
)

INSERT INTO @FeaturedContrib
SELECT TOP 6 ContribContent.UserID, ContribContent.Category, ContribContent.Title,     SUBSTRING(ContribContent.Article, 0, 200) AS Summary, ContribContent.DatePosted,     Users.FirstName, Users.LastName, Users.Picture
FROM ContribContent
INNER JOIN Users
ON ContribContent.UserID = Users.UserID
WHERE ContribContent.DateFeatured IS NOT NULL
ORDER BY ContribContent.DateFeatured DESC

SELECT * FROM @FeaturedContrib
END

There are two data tables involved:

Users - a table storing all of the users and their information.

ContribContent

THANKS to anyone who can help out!

Upvotes: 4

Views: 1523

Answers (1)

Vishal
Vishal

Reputation: 12369

Run only - 

SELECT TOP 6 ContribContent.UserID, ContribContent.Category, ContribContent.Title,     SUBSTRING(ContribContent.Article, 0, 200) AS Summary, ContribContent.DatePosted,     Users.FirstName, Users.LastName, Users.Picture
FROM ContribContent
INNER JOIN Users
ON ContribContent.UserID = Users.UserID
WHERE ContribContent.DateFeatured IS NOT NULL
ORDER BY ContribContent.DateFeatured DESC

See what you getting might be an issue with your Where or your join see carefully if you have any data in the first place being returned. My guess is join see if you have matching userids you are joining on...(Hint : Left join maybe your answer)

Upvotes: 2

Related Questions