My evolving relationship with technology and art

Monthly Archives: April 2012

Badb just asked me if I could show her how to calculate the square root of a number. I cast my mind back to 7th grade and tried to remember going up to the blackboard and calculating arbitrary square roots. “Um, yeah, but let me go look it up. It’s been a long time and I want to be sure I’m right,” I said. I found a nice explanation that aligned well with the method I learned oh-so-many years ago (and haven’t used since — what do you suppose slide rules, calculators, and general-purpose computers are for, if not for doing homework?) and came back to her. “Okay, so show me the number you’re supposed to take the square root of.”

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:

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.