I have three tables:
Table(attribute1, attribute2...);
---------------------------------
Users(iduser, username)
Link(idlink, title, userid)
Comment(idcomment, content, linkid, userid)
How to select: Link title, with corresponding username and number of comments?
I'm currently doing like this:
Q1-Select links (SELECT * FROM `links`)
Q2-Extract usernames from previous query(Q1) - (SELECT username FROM `user` WHERE iduser=Q1.userid
Q3-Extract number of comments from Q1 by id (SELECT COUNT(*) as comments FROM `comment` WHERE linkid='Q1.idlink')
I believe we can do this in much more optimized way. I got idea how to get Link with corresponding username but I got stuck when I need to count comments.