如何从多个表有效地访问用户数据

Question

Many forums and websites display user information on posts or in tables (when you are looking at more than 1 user)

For example you might have a forum thread and each post on it displays information about the the poster like this:

Name: John Email: [e-mail] Posts: 450

They probably have a table for users which is where the name and email are stored, and then a table for posts.

The only think I can think of to do it in one sql statement would be to add 1 to a row (the one that corresponds to the user making the post) in the user table each time the user posted which seems like a bad solution to me.

How do they count the number of posts for each user?

Answer 1

They use the SQL COUNT() function to dynamically generate the count:

SELECT COUNT( *) FROM posts WHERE user_id = 1

This will count the number of rows in the posts table which were written by users who match the criteria user_id = 1.

To do this in one query, you can use a JOIN, and GROUP BY the identifier, like so:

SELECT username, user_email, COUNT( p.post_id) as num_posts
    FROM users u
    LEFT JOIN posts p
    ON p.user_id = u.user_id
WHERE u.user_id = 1
GROUP BY u.user_id