MySQL multiple table SELECT

Question

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.

Marc B · Accepted Answer · 2012-03-01 17:11:21Z

4

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.

answered Mar 1, 2012 at 17:11

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ewooycom Over a year ago

Thanks, this is exactly what I wanted! How Am I supposed to construct comment then? I'm selecting userid from comment later when I'm selecting comments. Do you mean I just need to rename column name?

Marc B Over a year ago

Depends on your usage. Maybe you only allow ONE of linkid or userid to be set, so a single comment can only ever be attached to a user OR a link, but not both.

user1191247 Over a year ago

I think you have both missed the point here. A comment is made by one user about one link.

ewooycom Over a year ago

Comment is made by user on specific link, user can comment more than once, link is made by user, user can make more links.

Marc B Over a year ago

Then the linkage should be user->link->comment - one single foreign key pointing back at the immediate "ancestor" table. While there's nothing really WRONG with how you've done it (it saves a step trying to get info on a user who left a comment, by eliminating having to join in the link table), it does allow mis-attribution.

|

user1191247 · Accepted Answer · 2012-03-01 17:23:57Z

1

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`

answered Mar 1, 2012 at 17:23

user1191247

13k2 gold badges25 silver badges33 bronze badges

2 Comments

zerkms Over a year ago

"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." --- no it is not. None of RDBMS requires to put all the group by columns to the select. You can even select none of the group by fields but go with just SELECT COUNT(*)

user1191247 Over a year ago

Sorry. Poorly worded on my part. What I meant is that columns in the column list should either be in the GROUP BY clause or within some kind of aggregate function.

Adam Presley · Accepted Answer · 2012-03-01 17:10:16Z

0

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

answered Mar 1, 2012 at 17:10

Adam Presley

5494 silver badges6 bronze badges

2 Comments

Haim Evgi Over a year ago

he wants group by u.username and the sum of comments per user

user1191247 Over a year ago

Or does he want the count of comments per link? That is what Q3 would suggest.

Collectives™ on Stack Overflow

MySQL multiple table SELECT

3 Answers 3

8 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related