Generate Prefix Number ID with Condition By Group ID

Question

First this is my table

CREATE TABLE IF NOT EXISTS `group` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `member` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `group_id` int(11) unsigned NOT NULL,
    `code` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `group_id` (`group_id`),
    FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I need member.id look like 001, 002, 003. . i following lpad(id, 3, 0) and case close

But how to reset 001, 002, 003 back to 001 again where have different group_id

This query have result 01-001, 01-002, 02-003, 03-004 (group.id - member.id)

SELECT CONCAT(lpad(g.id, 2, 0),'-',lpad(m.id, 3, 0)) AS id, m.name
FROM member m
LEFT JOIN group g ON g.id = m.group_id
ORDER BY m.id ASC

Please help, i need 01-001, 01-002, 02-001, 03-001 (group.id - member.id)

Or can i use trigger to make my member.code auto generate when insert to 01-001, 01-002, 02-003, 03-004 base on group.id - member.id

Generate Prefix Number ID with Condition By Group ID

0 Answers0