CODEWITHSUNDEEP
phpmysqlsql
ewooycom
ewooycom

Reputation: 2721

MySQL multiple table SELECT

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.

Upvotes: 0

Views: 462

Answers (3)

user1191247
user1191247

Reputation: 12998

It is good practice to get into the habit of putting the fields you want into both the SELECT and GROUP BY clauses, that way it won't come as such a shock when you have to use an RDBMS that insists on it.

SELECT
    `l`.`idlink`,
    `l`.`title`,
    `u`.`username`,
    COUNT(`c`,`idcomment`) AS `comment_count`
FROM `links` `l`
INNER JOIN `users` `u`
    ON `l`.`userid` = `u`.`iduser`
LEFT JOIN `comments` `c`
    ON `l`.`idlink` = `c`.`linkid`
GROUP BY
    `l`.`idlink`,
    `l`.`title`,
    `u`.`username`

Upvotes: 1

Marc B
Marc B

Reputation: 360682

SELECT iduser, username, Link.title, COUNT(idcomment)
FROM Users
LEFT JOIN Link ON (iduser = userid)
LEFT JOIN Comment ON (linkid = idlink)
GROUP BY iduser, idlink

Note that your Comment table is somewhat badly designed - the 'userid' field is not necessary, and can actually lead to situation where you've got a cross-linked record. e.g. a Comment belonging to user A might could be linked to a Link record belonging to user B.

Upvotes: 4

Adam Presley
Adam Presley

Reputation: 549

SELECT
    l.idlink
    , l.title
    , l.userid
    , u.iduser
    , u.username
    , c.idcomment
    , c.content

FROM Link AS l
    JOIN Users AS u ON u.iduser=l.userid
    JOIN Comment AS c ON c.linkid=l.idlink

Upvotes: 0

Related Questions