Time Out for SQL

Basic SQL is pretty simple, really. It’s only when I start dealing with big joins and group functions that I start to lose track of what exactly is going on. I’m in that situation now, so I’m constructing a small data set with short table and column names so I don’t have to type as much while I figure out the right syntax. The general problem statement I’ve got is this:

Given a pair of tables where one table holds a bunch of records and the other table holds labels for groups of those records, construct a single SELECT statement that will select all of the records as a single output cell with the format: “label1:’record1′,’record2′,’record3′,label2:’record4′,’record5′,’record6′”.

I’m working with MySQL and intend to use some swell functions, namely CONCAT and GROUP_CONCAT. My initial demo setup is this:

create table demo_a (
  c_id    int NOT NULL,
  c_name  varchar(60) NOT NULL
);

create table demo_b (
  r_id    int NOT NULL,
  r_name  varchar(60) NOT NULL,
  c_id    int NOT NULL
);

insert into demo_a(c_id, c_name) values (1, 'first cohort');
insert into demo_a(c_id, c_name) values (2, 'second cohort');

insert into demo_b(r_id, r_name, c_id) values(1, 'Dave', 1);
insert into demo_b(r_id, r_name, c_id) values(2, 'Sunny Jim', 1);
insert into demo_b(r_id, r_name, c_id) values(3, 'Hoos-Foos', 1);
insert into demo_b(r_id, r_name, c_id) values(4, 'Paris Garters', 2);
insert into demo_b(r_id, r_name, c_id) values(5, 'Harris Tweed', 2);
insert into demo_b(r_id, r_name, c_id) values(6, 'Zanzibar Buck-Buck McFate', 2);

Okay, now this query will return six rows, showing the names and labels of each record:

SELECT b.r_name, a.c_name from demo_b b left join demo_a a on b.c_id = a.c_id;

Let’s see if we can get the output down to two rows, each consisting of a label and some concatenated names.

SELECT a.c_name, GROUP_CONCAT(b.r_name ORDER BY b.r_id SEPARATOR ', ') from demo_b b left join demo_a a on b.c_id = a.c_id GROUP BY b.c_id;

That works! Okay, now let’s see if we can get that down to a single column.

SELECT CONCAT(a.c_name, ": ", GROUP_CONCAT(CONCAT("'",b.r_name,"'") ORDER BY b.r_id SEPARATOR ', ')) from demo_b b left join demo_a a on b.c_id = a.c_id GROUP BY b.c_id;

Hey, that’s pretty good! Okay, now how do I get these two rows collapsed down into a single one? I wonder if I need to wrap this select in another select, defining the two-row result set from the above query as a table and then grouping all those rows together. This sort of matryoshka nesting of queries is what gives me SQL headaches.

SELECT GROUP_CONCAT(x.labeled_names SEPARATOR ', ') single_row FROM
(SELECT CONCAT(a.c_name, ": ", GROUP_CONCAT(CONCAT("'",b.r_name,"'") ORDER BY b.r_id SEPARATOR ', ')) labeled_names
 from demo_b b left join demo_a a on b.c_id = a.c_id GROUP BY b.c_id) x;

OMG it totally works! Yay!