There are two tables in one list of users, and in the other some data for each user, which are recorded periodically, in a completely different way for each user. How to choose from two tables: from the users table and from the linked table the latest linked record for each user (sorted by date added)? Or the second solution is to put the data in two tables at once - in the user’s line (update) and in the linked table (insert)? So I have fresh data in the user table itself and a separate table with a history of changes. What is the best?

3 answers 3

Maybe it will be true)) The users and user_records with user_records records associated with users via user_id

 SELECT u.id, u.`name`, ( SELECT `record` FROM user_records WHERE user_id = ur.`user_id` ORDER BY `datetime` DESC LIMIT 1) AS `last_record` FROM users u left JOIN user_records ur ON u.`id` = ur.`user_id` GROUP BY u.id

How to test http://sqlfiddle.com/#!9/e4be63/2

    We will search for the last record by the date field in which the date of adding the record to the user_data data user_data . You can also use the record ID from user_data if there is such a field there.

     select * from users U left join user_data D on D.user_id=U.user_id and D.date=(select date from user_data D1 where D1.user_id=U.user_id order by date desc limit 1 )

    In principle, it would be quicker to get data directly from the users table if we entered this latest data there. But this introduces excessive redundancy in the database, i.e. in principle, for the sake of performance, if problems are noticed with the above described request for this, you can go. But any redundancy causes a slower update of the data and possible errors like the one that the record was inserted into the user_data, and forgot to update the users. Or, from the user_data, we delete the last, erroneously added record, how to update the data in users - correctly, finding an older record, approximately by the same query as above ...

    • Hmm .... and my shell cursed the wrong syntax when I tried to select.....order by date desc to write to the join condition ..... so I had to do it through groups and submit a subquery to the top ..... hmm ...... probably the shell is stupid - Alex Shimansky
    • @ Alexey Shimansky Or maybe you wrote not date=(...) , but date in(...) ? MySQL does not allow the use of limit inside the in subquery, and in the case of the usual "equal" allows :) - Mike
    • none I didn’t even remember about IN at all, but he’s here as if he’s out of place) .... well, let's write everything down for midnight glitches - Alexey Shimansky
    • I agree about redundancy. I use transactions for such "paired" update additions. The data from the history table will not be deleted. Probably I’ll still do it by adding-changing to both tables, but I’m testing the requests I’ve offered, I’ve got a little experience, but these things are terribly interesting. I have a specific task. Such a "mini-bank". A narrow circle of users and a small number of operations. - Artem Tikhonovich
    • @ ArtyomTihonovich For a good trigger on user_data you are doing, he updates the user himself when inserting - Mike

    Let there be a users table with users and a user_id table associated with it via the user_id foreign key. Let the update date be stored in the tbl.updated_at field. Then you can get the users and the corresponding last records from the tbl table using the following query

     SELECT u.*, t1.* FROM users AS u LEFT JOIN tbl AS t1 ON u.id = t1.user_id LEFT JOIN tbl AS t2 ON u.id = t2.user_id AND t1.updated_at < t2.updated_at WHERE t2.updated_at IS NULL
    • Thank. I'll try. The result is I need a table of users with additional fields in which the data is the latest record from the related table. If there are no records associated, then these fields will be NULL. But the user account is needed in any case. - Artem Tikhonovich
    • one
      Then the LEFT JOIN is exactly what you need, all the records from the left table are present, regardless of whether there is a connection or not. If there is no connection, NULL is returned. - cheops
    • Yes exactly. Thank you very much. For me, the construction ON ... AND is not yet familiar. This is probably the sorting of the new record, as I understand it. - Artem Tikhonovich
    • one
      Not really. With ON, two tables are linked. Since we are dealing with sets, in order to sort the tables in the order we need, we resort to self-joining the tables, we join the tbl table twice (by assigning different names to it using AS). The t1.updated_at <t2.updated_at condition allows us to select the latest records from the t2 table. - cheops