Как объединить несколько операторов SQL select
#//SCHEMA CREATE TABLE `orgs` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `org_name` varchar(250) NOT NULL, UNIQUE (org_name) ) CREATE TABLE `orgs_relation` ( `org_id` int(10) UNSIGNED NOT NULL, `parent_org_id` int(10) UNSIGNED NOT NULL, CONSTRAINT org_relation_pair UNIQUE (org_id, parent_org_id) ) #// Get parents SELECT parent_org_id, orgs.org_name as org_name, "parent" as parent FROM `orgs_relation` JOIN orgs ON orgs_relation.parent_org_id = orgs.id WHERE org_id = (SELECT id FROM orgs WHERE org_name='Black Banana') ORDER BY org_name ASC #// Get chidren SELECT org_id, orgs.org_name as org_name, "children" as children FROM `orgs_relation` JOIN orgs ON orgs_relation.org_id = orgs.id WHERE parent_org_id = (SELECT id FROM orgs WHERE org_name='Black Banana') ORDER BY org_name ASC #// Get sisters SELECT DISTINCT or2.org_id AS sister_id, org_name, "sisters" as sisters FROM `orgs_relation` AS or1 JOIN orgs_relation AS or2 ON or1.parent_org_id = or2.parent_org_id JOIN orgs ON or2.org_id = orgs.id WHERE or1.org_id = (SELECT id FROM orgs WHERE org_name='Black Banana') ORDER BY org_name ASC
Что я уже пробовал:
var q = "SELECT org_id, orgs.org_name as org_name, 'daughters' as daughters FROM `orgs_relation` JOIN orgs ON orgs_relation.org_id = orgs.id WHERE parent_org_id = (SELECT id FROM orgs WHERE org_name = 'Black Banana');SELECT parent_org_id, orgs.org_name as org_name, 'parent' as parent FROM `orgs_relation`JOIN orgs ON orgs_relation.parent_org_id = orgs.id WHERE org_id = (SELECT id FROM orgs WHERE org_name= 'Black Banana');SELECT DISTINCT or2.org_id AS sister_id, org_name, 'sisters' as sisters FROM `orgs_relation` AS or1 JOIN orgs_relation AS or2 ON or1.parent_org_id = or2.parent_org_id JOIN orgs ON or2.org_id = orgs.id WHERE or1.org_id = (SELECT id FROM orgs WHERE org_name= 'Black Banana')"; connection.query(q, function (error, results) { if (error) throw error; // console.log(results[0]); // console.log(results[1]); // console.log(results[2]); res.send(results); }); });