Trigger based on linked table OK I have 3 tables:

client:

ClientID (int)auto-inc

clientName (varchar)

tnt_group:

groupID (int)auto-inc

ClientID (int)foreign-key

tnt_item:

itemID (int)auto-inc

itemNo (int)

groupID (int)foreign-key

Now, I need to generate an itemNo for each record in tnt_item, starting from 1 for each ClientID so each client see's their item count starting from 1. I have tried adding a trigger as follows:

BEGIN
    SET NEW.itemNo = (
        SELECT IF(t.itemNo IS NULL, 1, IFNULL(MAX(t.itemNo), 0) + 1)
        FROM (
            SELECT IFNULL(i.itemNo, 0) AS itemNo, g.groupID, g.ClientID, cg.ClientID AS cgClient, cg.groupID  AS cgGroup
            FROM tnt_group cg
            INNER JOIN tnt_group g ON cg.ClientID = g.ClientID
            INNER JOIN tnt_item i ON i.groupID = g.groupID
        ) AS t WHERE t.cgGroup = NEW.groupID
        GROUP BY cgClient
    );
END


This is working, in the case where a client already has at least 1 asset, it gets the max itemNo then adds one. where it is failing, is if a client does not have any items yet, no matter what I do, it sets the first itemNo as NULL

Any tips on what i am doing wrong?
CREATE TRIGGER tr
BEFORE INSERT
ON tnt_item
FOR EACH ROW
SET NEW.itemNo = 1 + COALESCE((SELECT MAX(t1.itemNo)
                               FROM tnt_item t1, tnt_group t2, tnt_group t3
                               WHERE t1.groupID = t2.groupID
                                 AND t2.ClientID = t3.ClientID
                                 AND t3.groupID = NEW.groupID), 0);


fiddle