SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE knd_apil ( id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, parent_order_id BIGINT, name VARCHAR(100) ); -- Первичная жалоба 1 INSERT INTO knd_apil ( id, order_id, parent_order_id, name) VALUES (1, 1001, NULL, 'Причина первичной жалобы 1'); -- Первичная жалоба 2 INSERT INTO knd_apil ( id, order_id, parent_order_id, name) VALUES (2, 1002, NULL,'Причина первичной жалобы 2'); INSERT INTO knd_apil ( id, order_id, parent_order_id) VALUES (3, 1003, 1001); INSERT INTO knd_apil ( id, order_id, parent_order_id) VALUES (4, 1004, 1002); SELECT parent.id, parent.order_id, parent.parent_order_id, child.name as childname, parent.name as parentname, child.id, child.order_id, child.parent_order_id, COALESCE(child.Name,parent.Name ) FROM knd_apil parent LEFT JOIN knd_apil child ON parent.order_id= child.parent_order_id ; SELECT child.id, child.order_id, child.parent_order_id, child.name as childname, parent.name as parentname, COALESCE(child.Name, parent.Name) AS Name FROM knd_apil child LEFT JOIN knd_apil parent ON child.parent_order_id = parent.order_id; SELECT DISTINCT name AS title, order_id , id FROM knd_apil WHERE name = 'Причина первичной жалобы 1' OR order_id IN (SELECT parent_order_id FROM knd_apil ); SELECT parent_order_id FROM knd_apil; SELECT DISTINCT name AS title, order_id , id FROM knd_apil WHERE name = 'Причина первичной жалобы 1' UNION ALL SELECT 1 AS title, 2 AS id FROM knd_apil where parent_order_id IS NOT NULL and parent_order_id in (SELECT DISTINCT order_id FROM knd_apil WHERE name = 'Причина первичной жалобы 1')