MySql – joining tables and getting updated result when one table is updated

Could you please help with this query? I just have started learning SQL, I cannot see where my mistake is.

I have tables : USERS (columns: id, firstname and surname) and POSTS(columns:id, user_id and BODY). I want to have a joined table which would reflect the count of users that have posted at least 2 times. So I created a table POSTSBYNUMBER. Then I used INSERT as follows:

INSERT INTO POSTSBYNUMBER
    SELECT USERS.FIRSTNAME, USERS.LASTNAME, COUNT(*) AS POSTS_NUMBER
    from USERS
    JOIN POSTS ON USERS.ID = POSTS.USER_ID
    GROUP BY POSTS.USER_ID
    HAVING COUNT(*) >= 2;

The table looks all right: has columns FIRSTNAME, SECONDNAME and Posts_Numbers. But when (using java), I added two new posts for a third user, the post is reflected in table POSTS, but not in POSTSBYNUMBER. Is there any issue with how I have written joining tables in SQL?

Answer

An INSERT happens once when you run it. You would seem to want a view instead:

CREATE VIEW POSTSBYNUMBER AS
    SELECT u.FIRSTNAME, u.LASTNAME, COUNT(*) AS POSTS_NUMBER
    FROM USERS u JOIN
         POSTS p
         ON u.ID = p.USER_ID
    GROUP BY u.FIRSTNAME, u.LASTNAME
    HAVING COUNT(*) >= 2;

A view is a “stored query”. So, the query runs when you execute it. That way, the data is always up-to-date.

Notice that I also fixed the GROUP BY so it is consistent with the unaggregated SELECT columns.