Question

mySQL database question.. I have a database that has the following tables:

User (Id, Name, Gender) Primary key = Id

Friends (Id1, Id2, Startdate) Primary key = (Id1, Id2) Foreign keys are also Id1, Id2 pointing to User(Id)

Comments (CommentId, Poster, Recipient, Text, PostDate) Primary key = (CommentId) Foreign Keys are Poster, Recipient pointing to User(Id)

I need to answer the following queries:

3. Find users who have commented at least twice to the same user

4. For each user who had commented at least twice, list the text and the postdate of comments to friends.

When finding users, all I need to list is the Id of the user.

Answer 1

#rate my solution and comment if any doubts

3.

select c.poster from comments as c join comments as d on c.poster=d.poster and c.recipient =d.recipient and c.commentid<>d.commentid group by c.poster having count(*)>1;

#comments table is joined with it self to check weather the user commented twice or not...group by is done to group all the comments posted by single user

4.

select c.poster,c.text,c.postdate from (select * from comments join friends on (poster=id1 and recipient=id2) or(poster=id2 and recipient=id1)) as c join (select * from comments join friends on (poster=id1 and recipient=id2) or(poster=id2 and recipient=id1)) as d on c.poster=d.poster and c.recipient=d.recipient and c.commentid<>d.commentid;

# I considered that if a id in id1 column is friend of a id in id2 column then vice versa are friends as well

#c table have all friends and their comments same as d.

#joinig c and d on as same poster and same recipient and different commentid