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

Navigation
0

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
source

1 answer

0

You can make the following query: 

SELECT
    u.user_id,
    u.first_name
    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
Date Format PhpSpreadSheet (Android) Activate several Radiobuttons from different RadioGroups by just swiping their finger, without the need to click on them