MySQL Query Examples

MySQL examples and queries:

http://www.artfulsoftware.com/infotree/queries.php

This great resource showed me a simple way to pivot in MySQL.  If you have the following tables:

CREATE TABLE `project` (
  `project_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`project_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `project_service` (
  `project_id` int(11) NOT NULL,
  `service_id` int(11) NOT NULL,
  UNIQUE KEY `project_id` (`project_id`,`service_id`),
  KEY `service_id` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `service` (
  `service_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `short_name` varchar(5) NOT NULL,
  PRIMARY KEY  (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The result I wanted was to show a project name and its services all in one row.  To accomplish this the website said to use GROUP_CONCAT like so:

select
    p.name,
    GROUP_CONCAT(if(s.service_id = 1, '1', NULL)) AS 'Service1',
    GROUP_CONCAT(if(s.service_id = 2, '1', NULL)) AS 'Service2',
    GROUP_CONCAT(if(s.service_id = 3, '1', NULL)) AS 'Service3'
  from project as p
  inner join project_service as ps
    on (p.project_id = ps.project_id)
  inner join service as s
    on (s.service_id = ps.service_id)

With that query I can see a 1 for each of the project’s services.  Please see the pivot section for more details.