SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE customers( id INT UNSIGNED NOT NULL, f_name VARCHAR(80) NOT NULL, l_name VARCHAR(80) NOT NULL, PRIMARY KEY(id) ); CREATE TABLE surnom( id_surnom INT UNSIGNED NOT NULL AUTO_INCREMENT, id_customers INT UNSIGNED NOT NULL REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE, surnom VARCHAR(80), PRIMARY KEY(id), UNIQUE KEY(surnom) ); INSERT INTO customers(id, f_name, l_name) VALUES(1, 'julp (p)', 'julp (n)'); INSERT INTO customers(id, f_name, l_name) VALUES(2, 'Z2V (p)', 'Z2V (n)'); INSERT INTO surnom(id_customers, surnom) VALUES(1, 'julp'); INSERT INTO surnom(id_customers, surnom) VALUES(2, 'Z2V'); INSERT INTO surnom(id_customers, surnom) VALUES(2, 'Z2V2'); INSERT INTO surnom(id_customers, surnom) VALUES(1, 'julp1'); INSERT INTO surnom(id_customers, surnom) VALUES(1, 'julp2'); SELECT t.*, GROUP_CONCAT(`surnom`) AS surnoms FROM ( SELECT f_name, l_name, numero FROM customers LEFT JOIN surnom ON customers.id = surnom.id_customers WHERE CONCAT(numero, f_name,l_name, surnom) LIKE 'James' ORDER BY id DESC ) t GROUP BY f_name, l_name, numero ;