There are 3 tables:

CREATE TABLE `item` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` varchar(50) NOT NULL, `code` varchar(14) NOT NULL, `workname` varchar(255) NOT NULL, `markedflag` tinyint(1) NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `price` ( `id` bigint(20) UNSIGNED NOT NULL, `item_id` bigint(20) UNSIGNED NOT NULL, `doc_id` bigint(20) UNSIGNED NOT NULL, `price` double NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `doc` ( `id` bigint(20) UNSIGNED NOT NULL, `code` int(8) UNSIGNED NOT NULL, `docdate` datetime NOT NULL, `user_id` varchar(50) NOT NULL, `apply` tinyint(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • in item is the output associated with a specific user through user_id;
  • in doc there are records (documents) that are also bound to the user via user_id and have the date of creation of docdate;
  • in the price is the price itself which is associated with the product through item_id and is associated with the document through doc_id;

Through the creation of a new document, the price of the product is updated, all products and only one position can be updated. From all this you need to get a list of all products for a specific user with the latest prices. The result should be a item.id , price.price , doc.id , doc.docdate

Thanks in advance to everyone who will help :)

    3 answers 3

    If I understand correctly, is something needed?

     select i.id, p.price, d.id, d.docdate from item i inner join price p on (i.id = p.item_id) inner join doc d on (d.user_id = i.user_id) and (d.id = p.doc_id) where d.id in (select id from doc where user_id = 'Сюда id юзера' group by id having max(docdate) > 1)
    • Why not where a.user_id = ? - ilyaplot
    • The joke is that if you don’t put up in where, all the lines from item and price that are not even related to the user will be selected. And I would add inner to join - ilyaplot
    • And I would change the pseudonyms to more meaningful, a/b/c are no good) i/p/d much easier to understand. - vp_arth
    • I agree, there is room to grow. ) Corrected. - Elfilagas
    • in the line with inner join doc d on (d.user_id = i.user_id) you need to add d.id = p.doc_id then the query works correctly, but not quite as needed :) ... he selects all prices from the documents, but only the actual (last) price is needed ... you need to take the product id, look at the documents it is in and take the price from the document with the most recent date - Interruption

    If I understood correctly, then such a query works and gives the correct result.

     SELECT p.[item_id] ,p.[doc_id] ,p.[price] ,d.docdate FROM [price] p, [doc] d where d.Id = p.[doc_id] AND p.[doc_id] in (SELECT TOP 1 [Id] FROM [doc] WHERE [user_id] = @user_id ORDER BY docdate] ASC )

    This is just the starting point. This query will be executed in any SQL standard (I checked it), but you need to bring it to the usual form (Iner Join) for MySql, which I don’t have now at hand.

    • Unfortunately, my knowledge is not enough to translate your code in MySQL, as far as I understood in the subquery, you simply pull out the id of the newest document from a specific user, and as I wrote in the comments earlier ... ... you need to take the product id, look in what documents is it present and take the price from the document with the most recent date - Interruption
    • @Interruption, pancake - pancake, I misunderstood your task - you need to get not the latest update for the user, but an update for all products, then without Max (Date) and Having grouped by products you can not do, so my workpiece needs to be redone . I will try to do it, but I can do it only tomorrow morning - Alexander Muksimov

    After comprehending the requests of Elfilagas and Alexander Muksimov made such a request, it works as it should, but it seems that it is far from optimal :) ... therefore I still hope that someone will suggest a better option.

     SELECT nm.`id`, if(T2.`doc_id` is not null,T2.`doc_id`,'н/д') as `doc_id`, if(T2.`price` is not null,T2.`price`,'н/д') as `price`, if(T2.`docdate` is not null,T2.`docdate`,'н/д') as `docdate` FROM `item` nm LEFT JOIN (SELECT i.`id`, np.`price`, np.`doc_id`, dc.`docdate` FROM `item` i, `price` np, `doc` dc WHERE i.`user_id` = '1111111111' AND np.`item_id` = i.`id` AND np.`doc_id` = (SELECT d.`id` FROM `doc` d, `price` n WHERE d.`user_id` = i.`user_id` and n.`item_id` = i.`id` and n.`doc_id` = d.`id` ORDER BY d.`docdate` DESC LIMIT 1) AND dc.`id` = np.`doc_id`) AS T2 ON T2.`id` = nm.`id` WHERE nm.`user_id` = '1111111111'
    • Well, if you were able to take advantage of my workpiece, then add me a rating :) by clicking on the gray up arrow or by voting for my question, which you will find in my profile. - Alexander Muksimov