GROUP_CONCAT_HELP-2, MySQL

Language: Layout:

drop table if exists genealogy_orgs;
drop table if exists genealogy_relations;
drop table if exists genealogy_users;

-- start script
CREATE TABLE `genealogy_orgs` (
  `org_id` varchar(255) NOT NULL,
  `org_name` varchar(255) DEFAULT NULL,
  `org_city` varchar(255) DEFAULT NULL,
  `org_state` varchar(255) DEFAULT NULL,
  `org_country` varchar(255) DEFAULT NULL,
  `total_donations` int(11) DEFAULT NULL,
  `total_donated` int(11) DEFAULT NULL,
  PRIMARY KEY (`org_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO genealogy_orgs (org_id,org_name,org_city,org_state,org_country,total_donations,total_donated) VALUES
('77505','Mayo Clinic','Rochester','MN','','0','0'),
('82171','Hospital for Special Surgery','New York','NY','','0','0'),
('84864','Twin Cities Spine Center','Minneapolis','MN','','0','0'),
('97986','Children\'s Hospital of Philadelphia','Philadelphia','PA','','0','0'),
('98211','Stanford University School of Medicine','Redwood City','CA','','0','0'),
('98317','University of Rochester','Rochester','NY','','0','0'),
('99068','Weill Cornell Medical College ','New York ','NY','','0','0'),
('99143','Washington University','Saint Louis ','MO','','0','0');
-- end script

-- start script
CREATE TABLE `genealogy_relations` (
  `user_id` varchar(25) DEFAULT NULL,
  `org_id` varchar(25) DEFAULT NULL,
  `relation_type` varchar(255) DEFAULT NULL,
  `start_year` varchar(4) DEFAULT NULL,
  `end_year` varchar(4) DEFAULT NULL,
  `current_org` varchar(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO genealogy_relations (user_id,org_id,relation_type,start_year,end_year,current_org) VALUES
('00001','84864','FELLOW','1983','1984','1'),
('00002','84864','FELLOW','1990','1991','1'),
('00003','84864','FELLOW','1984','1985','1'),
('00003','82171','FELLOW','1988','1989','0'),
('00003','99143','FELLOW','1986','1986','0');
-- end script

-- start script
CREATE TABLE `genealogy_users` (
  `imis_id` varchar(255) NOT NULL,
  `member_type` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `member_email` varchar(255) DEFAULT NULL,
  `past_president` varchar(10) DEFAULT NULL,
  `founding_member` varchar(10) DEFAULT NULL,
  `home_page` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`imis_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO genealogy_users (imis_id,member_type,first_name,last_name,designation,full_name,member_email,past_president,founding_member,home_page) VALUES
('00001','member','John','Doe','MD','John Doe, MD','johndoe@doe.com','yes','yes','yes'),
('00002','member','Jane','Doe','MD','Jane Doe, MD','janedoe@doe.com','no','yes','yes'),
('00003','member','Jimmy','Doe','MD','Jimmy Doe, MD','jimmydoe@doe.com','yes','no','yes');
-- end script

SELECT genealogy_users.*, genealogy_orgs.org_id, genealogy_orgs.org_name, genealogy_relations.user_id, genealogy_relations.relation_type, genealogy_relations.start_year, genealogy_relations.end_year, genealogy_users.home_page,
(SELECT GROUP_CONCAT(org_id,'-',org_name,'-',start_year,'-',end_year,'^') FROM genealogy_relations WHERE genealogy_relations.user_id = genealogy_users.imis_id) AS org_list
FROM genealogy_users 
INNER JOIN genealogy_relations ON genealogy_users.imis_id = genealogy_relations.user_id 
INNER JOIN genealogy_orgs ON genealogy_relations.org_id = genealogy_orgs.org_id 
WHERE genealogy_users.imis_id = '00003';


absolute service time: 0,36 sec