I am building a Hamper website for a local church for their Christmas hamper drive, and I am looking for some guidance on how to achieve the desired MySQL query / result. The intended page is just a client index, but it shows the current / active hamper_no ( C(ouple)001 ), name, address, phone number,... And In order to have the correct information, I wrote a query, first SELECT ... FROM clients as c JOIN hampers as h ON ... To keep it simple, the (first) clients table looks like this:

And the hampers table looks as follows:

The desired query looks something like this.

SELECT h.`id` AS h_id, h.`client_id` AS c_id, IF(YEAR(h.`created_date`)=2022, h.`hamper_no`, NULL) AS hamper_no, YEAR(h.`created_date`) AS h_year, c.`id`, `hamper_id`, `name` FROM `clients` AS c LEFT JOIN `hampers` AS h ON (c.`id` = h.`client_id` AND IF(c.`hamper_id` IS NULL, NULL, c.`hamper_id`) = h.`id`) ORDER BY `name`; 

When it should look like this:

The problem however is that the c.hamper_id could be NULL (detached from the hampers). I am looking for a result similar to the one just above, where I can have a distinct result with the most recent hamper/client match. This will be used for multiple clients.

How can I narrow down the result to just 1 hamper per client from potentially many, without the use of c.hamper_id or with the use of a condition?

Another note to mention is that this is a client index, and there may not be any hampers to join either... so null values would be desirable for the JOIN columns.

CodePudding user response：

Based on https://stackoverflow.com/a/2111420/3095210

This is an example of the greatest-n-per-group problem that has appeared regularly on StackOverflow.

SELECT h1.`id`                                                  AS h_id,
       h1.`client_id`                                           AS c_id,
       IF(YEAR(h1.`created_date`) = 2022, h1.`hamper_no`, NULL) AS hamper_no,
       YEAR(h1.`created_date`)                                  AS h_year,
       c.`id`,
       `hamper_id`,
       `name`
FROM `clients` AS c
         LEFT JOIN `hampers` AS h1
              ON c.`id` = h1.`client_id`
         LEFT OUTER JOIN `hampers` AS h2
                         ON (
                                     c.id = h2.client_id
                                 AND
                                     (
                                                 h1.created_date < h2.created_date
                                             OR
                                                 (
                                                             h1.created_date = h2.created_date
                                                         AND h1.id < h2.id
                                                     )
                                         )
                             )
WHERE h2.id IS NULL
ORDER BY `name`;