CODEWITHSUNDEEP
sqlasp.netsql-server
Veslor
Veslor

Reputation: 80

SQL Server INNER JOIN with three tables

I have the following table structure :

News (Id_news, news_name)

Comments (Id_comment(PK),Id_news(FK), comment)

Replies(Id_Reply(PK), Id_Comment(FK), reply)

I want to get the number of replies to comments in the news.

This is the query I've tried.

SELECT COUNT(*)
FROM (News INNER JOIN Comments ON News.Id_news = Commnets.Id_Comment)
INNER JOIN Reply ON Commnets.Id_Comment = Reply.Id_Comment
WHERE News.Id_news = {0}

This this the following situation: I have a web pages with a lot of news, but not all the news have comments and not all the comments have replies. So I need to count if there is any reply in a specific news (e.g. Id_news = 43), to run one code or another.

Upvotes: 0

Views: 280

Answers (3)

Zerotoinfinity
Zerotoinfinity

Reputation: 6530

As you have mentioned that you want to have a count of replies for any particular news which may or may not have any comment or reply. Here is my try

SELECT COUNT(Replys.Id_Reply)
FROM NEWS  
LEFT JOIN Coments  ON Coments.ID_News = Coments.Id_News AND NEWS.Id_News = {0}
LEFT JOIN Replys  ON Replys.Id_Comment = Coments.Id_Comment

Sample code to try 

CREATE TABLE NEWS (Id_news INT, news_name varchar(10))
CREATE TABLE Coments (Id_comment INT,Id_news INT, comment varchar(10))
CREATE TABLE Replys(Id_Reply INT, Id_Comment INT , reply varchar(10))

INSERT INTO NEWS VALUES (1,'News1'), (2, 'News2'), (3,'News3')
INSERT INTO Coments VALUES (1,1,'Comment1'), (2,1,'Comment2'), (3,2,'Comment3')
INSERT INTO Replys VALUES (1,1,'Reply1'),(2,3,'Reply2'), (3,3,'Reply3')

Query:

DECLARE @NewId INT = 2
SELECT COUNT(Id_Reply)
FROM NEWS N 
LEFT JOIN Coments C ON C.ID_News = N.Id_News AND N.Id_News = @NewId
LEFT JOIN Replys R ON R.Id_Comment = C.Id_Comment

Check on SQL Fiddle

Upvotes: 4

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

Try this..

SELECT   News.Id_news,COUNT(*)
FROM    News 
JOIN    Comments 
    ON  News.Id_news = Commnets.Id_news
JOIN    Reply       
    ON  Commnets.Id_Comment = Reply.Id_Comment
WHERE   News.Id_news = {0}
group by News.Id_news,Commnets.Id_Comment

Upvotes: 0

bakersman
bakersman

Reputation: 68

I think your joining on to the wrong id in your first JOIN. Try

SELECT 
    COUNT(*)
FROM 
    News JOIN Comments ON News.Id_news = Commnets.Id_news
        JOIN Reply ON Commnets.Id_Comment = Reply.Id_Comment
WHERE 
    News.Id_news = {0}

Upvotes: 0

Related Questions