Get number of rows that contain a certain value, and that value put in another table


How about? After some search in stack overflow, I could not find the solution to the next problem.

I have the following table, in which there is a column where the number of postings that person has to his name in another table will hurt.

This would be the user table.

| user_id | first_name | posts |
| 1       | John       | xxxxx | <-- Ahi iria la cantidad de posts.
|_________|____________|_______|     En este caso 4.

This would be the table of posts

| post_id | author_id | title |
| 1       | 1         | test1 |
| 2       | 1         | test2 |
| 3       | 1         | test3 |
| 4       | 1         | test4 |

Author id is a foreign key to the user table where it refers to the user with id 1 (John).

If you wanted the value of the user john in the column of posts, be the number of rows that author_id have their id, how would you do? Could it be done that is updated automatically? Thanks in advance

asked by Teo villa 11.11.2018 в 00:34

1 answer


You can make the following query:

    COUNT(p.post_id) posts 
FROM usuarios u
    INNER JOIN posts p ON u.user_id=p.author_id
GROUP BY u.user_id
WHERE u.user_id=1;

Here the combination of COUNT and GROUP BY will allow to count the total of posts of the author and show a single row per author. The two tables are joined by the primary / foreign keys.

u and p are abbreviated aliases of tables. I do not use as for aliases, because it is allowed to do so. At least it seems more elegant to me.

The WHERE is for filtering by a specific author. If you remove it, it will tell you the posts for each author.

Regarding your question about:


Could you have it updated automatically?

I do not know what you mean, but it may be a subject for another question, after investigation on your part.

answered by 11.11.2018 в 01:30