CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
elgamal
elgamal

Reputation: 35

Get data from 3 database tables

I have 3 tables

  1. Users - columns id, userName,...
  2. Comment - columns id, text, userId, roomId
  3. CommentRate - columns userId, commmentId

CommentRate table contain userId's that liked this comment identified by commentId.

I want to select all comments (id, text, roomId, userId, userName, []ListOfLikes) from Comment table by roomId where []ListOfLikes should contain userId, userName.

Upvotes: 1

Views: 55

Answers (1)

kiks73
kiks73

Reputation: 3758

You can refer the same table ( USER in this case) more times with different join criteria using aliases, but It is not possible to convert values in field names. This statement will extract all that you need, but repeating rows and not adding columns:

SELECT C.ID, C.TEXT, C.ROOMID, U.USERNAME, U2.USERNAME 
FROM  COMMENT C 
INNER JOIN COMMENTRATE CR ON C.ID = CR.COMMENTID
INNER JOIN  USERS U ON C.USERID = U.ID
INNER JOIN  USERS U2 ON CR.USERID = U2.ID
WHERE U.USERNAME IN ('username1', 'username2')
ORDER BY C.ROOMID

If you want to filter by users that likes, you could replace U with U2 in the WHERE line of code.

Upvotes: 1

Related Questions