{"id":368,"date":"2011-05-28T16:02:28","date_gmt":"2011-05-29T02:02:28","guid":{"rendered":"http:\/\/mymonkeydo.com\/?p=368"},"modified":"2011-05-28T16:02:28","modified_gmt":"2011-05-29T02:02:28","slug":"mysql-query-examples","status":"publish","type":"post","link":"https:\/\/mymonkeydo.com\/mysql-query-examples\/","title":{"rendered":"MySQL Query Examples"},"content":{"rendered":"
http:\/\/www.artfulsoftware.com\/infotree\/queries.php<\/a><\/p>\n This great resource showed me a simple way to pivot in MySQL. \u00a0If you have the following tables:<\/p>\n The result I wanted was to show a project name and its services all in one row. \u00a0To accomplish this the website said to use GROUP_CONCAT like so:<\/p>\n With that query I can see a 1 for each of the project’s services. \u00a0Please see the pivot section<\/a> for more details.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","footnotes":""},"categories":[2],"tags":[51],"yoast_head":"\nCREATE TABLE `project` (\n `project_id` int(11) NOT NULL auto_increment,\n `name` varchar(50) NOT NULL,\n PRIMARY KEY (`project_id`),\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;\n\nCREATE TABLE `project_service` (\n `project_id` int(11) NOT NULL,\n `service_id` int(11) NOT NULL,\n UNIQUE KEY `project_id` (`project_id`,`service_id`),\n KEY `service_id` (`service_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\nCREATE TABLE `service` (\n `service_id` int(11) NOT NULL auto_increment,\n `name` varchar(50) NOT NULL,\n `short_name` varchar(5) NOT NULL,\n PRIMARY KEY (`service_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;<\/pre>\n
select\n p.name,\n GROUP_CONCAT(if(s.service_id = 1, '1', NULL)) AS 'Service1',\n GROUP_CONCAT(if(s.service_id = 2, '1', NULL)) AS 'Service2',\n GROUP_CONCAT(if(s.service_id = 3, '1', NULL)) AS 'Service3'\n from project as p\n inner join project_service as ps\n on (p.project_id = ps.project_id)\n inner join service as s\n on (s.service_id = ps.service_id)<\/pre>\n