[SOLVED] SQL - How to get a user's favorite posts title from StackExchange Database

SQL - How to get a user's favorite posts title from StackExchange Database

I am working on a project analyzing Stackoverflow's user's behaviors and I need to get data from the stackoverflow, one of the data I need to get is to get a user's favorite posts title from stackexchange Database. Right now, I only figured out how to get the user's favorite information as following:

select VT.name, U.DisplayName
from VoteTypes VT, Votes V, Users U
where VT.Id = 5 and
           VT.Id = V.VoteTypeId and
           U.Id = V.UserId and 
           U.Id = '85597'

I think the next step is to find the posts the User voted, then match the userId. But I don't know if it is a right step to do. And I could not find any connection between a User's vote and a question post

Solution

SELECT
      vt.name
    , u.DisplayName
FROM VoteTypes vt 
   , Votes v        <<<<<<<<< not good
   , Users u        <<<<<<<<< not good
WHERE vt.Id = 5
AND vt.Id = v.VoteTypeId  <<<<<<<<< join predicate (vt = v)
AND u.Id = v.UserId       <<<<<<<<< join predicate (u = v)
AND u.Id = '85597

Just STOP using commas between table names.'That simple step will force you to more carefully consider the joins.

Move the join predicates to the table they refer to AFTER an ON. (Join predicates refer to different tables either side of an operator such as equals.)

SELECT
      vt.name
    , u.DisplayName
FROM VoteTypes vt
   , Votes v ON vt.Id = v.VoteTypeId
   , Users u ON u.Id = v.UserId
WHERE vt.Id = 5
AND 
AND 
AND u.Id = '85597'

Replace those dreaded commas with appropriate join type, and tidy-up.

SELECT
      vt.name
    , u.DisplayName
FROM VoteTypes vt
INNER JOIN Votes v ON vt.Id = v.VoteTypeId
INNER JOIN Users u ON u.Id = v.UserId
WHERE vt.Id = 5
AND u.Id = '85597'

Done.

If your course is using old syntax; consider getting a better course.