Geek Answers Handbook

SELECT statement with WHERE clause and INNER JOIN clause

I am sure that I should make a trivial mistake here, but I was looking for help in this problem, and all I can find is information on conditional INNER JOINs.

<EDIT> The problem is that this stored procedure does not return anything. If I print only:

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

In the console, I get the return values. So this should be due to the inner connection? </EDIT>

The idea is as follows:

  • take the content that was featured (DateFeatured NOT NULL) and put it all in a temporary table
  • Get user names and images from the user table and map them to the values ​​in the temporary table using the UserID value.
  • sort the temporary table in order of the date on which each post was posted.
  • select the top six entries from the table

Here is 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:

Users - a table in which all users and their information are stored.

  • UserID INT
  • FirstName varchar (50)
  • LastName varchar (50)
  • Image of varchar (50)
  • etc.

Contribcontent

  • ContribContentID INT
  • UserID INT
  • INT Category
  • Varchar header (100)
  • Article varchar (MAX)
  • Image of varchar (50)
  • DatePosted Date
  • DateFeatured date
  • Remote bit

THANKS to everyone who can help!

+4
source share
1 answer

Launch 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 if there may be a problem with your Where or your join look carefully if you have data to be returned. My guess is: join see if you have the corresponding user IDs to which you are joining ... ( Tip : maybe you can join the left flank)

+2
source

More articles:


All Articles