php - How to use a conditional JOIN ON where the column can be NULL?

View Original

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:

id	hamper_id (index) DEFAULT NULL	name
1	2	DOE, John
2	null	DOE, Jane
3	null	DOE, Jack

Answer

Solution:

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`;

h_id	c_id	hamper_no	h_year	id	hamper_id	name
null	null	null	null	3	null	DOE, Jack
3	2	F001	2022	2	null	"DOE, Jane"
2	1	C012	2022	1	2	"DOE, John"

Source