Access 2 levels up - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Access 2 levels up

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 15:32:16

Hello,

I have 3 tables as follows:
[Document] > DocumentId (PK)
[Comment] > CommentId (PK), DocumentId (FK)
[Rate] > RateId (PK), CommentId (PK)

I know how to get all Rates for a certain CommentId.

But, how to get all the Rates which are related to Comments that are related to a given DocumentId?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 16:05:31

SELECT *
FROM Comment AS c
INNER JOIN Rate AS r ON r.CommentID = c.CommentID

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 17:46:29

Hi Peter,

Maybe I explained it wrong or maybe I am not understanding your code.

What I want is to provide a DocumentId.
Then get the rates related with the comments that are related with the DocumentId I provided.

Basically, the idea is:
1. Provide DocumentId
2. Find all comments with that DocumentId
3. Find all rates related with those found comments.
So basically I am getting all the rates related to that DocumentId.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 17:49:33

Add

WHERE c.DocumentID = @DocumentID

at the end of query, like this:

SELECT *
FROM Comment AS c
INNER JOIN Rate AS r ON r.CommentID = c.CommentID
WHERE c.DocumentID = @DocumentID

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 18:16:32

Hi,

I have been trying this but it is not working.
It's my fault. I didn't think this right.

Basically, I need to get only the RateId column from table Rates.
I need to select the rates which are related to comments that are related to the given DocumentId (Well, this is the same)

Then for all these RateIds I need to get their names from a fourth table named RateNames (which PK is RateId).
Finally I return a table with RateIds and RateNames.

Is this possible?
I tried to use two Inner Joins ... it didn't work but I don't even know if this is possible.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 18:23:21

SELECT r.RateID, n.RateName
FROM Comment AS c
INNER JOIN Rate AS r ON r.CommentID = c.CommentID
INNER JOIN RateNames AS n ON n.RateID = r.RateID
WHERE c.DocumentID = @DocumentID

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 19:07:44

Sorry Peter,

Just one more question about this:

I would like to create a third column named RateCount.

Basically, I need to get all RateIds available but also how many times they occur in [Rates] table.

I have been trying COUNT(c.RateID) but no success.
I used c because it's in Comment table where I need to count how many comments are associated to each RateId.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 19:11:54

SELECT r.RateID, n.RateName, COUNT(*)
FROM Comment AS c
INNER JOIN Rate AS r ON r.CommentID = c.CommentID
INNER JOIN RateNames AS n ON n.RateID = r.RateID
WHERE c.DocumentID = @DocumentID
GROUP BY r.RateID, n.RateName
ORDER BY 3 DESC, 2, 1

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-29 : 21:53:41

Peter,

Thank you very much for your help!

Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources