The post I refer most to on nostuff.org is the one where I explain – badly – how to do find/replace in SQL. It says a lot. As I say there, SQL is one of those simple things I rarely use, but when I do often feel the easy way to do something is out of my reach.
My latest endeavour looks a little like this and I thought it was worth recording, so my futre self can too look back on this for misguided reference.
SELECT e.itemid, COUNT(n.itemid) as authornum, type, title, date_year, group_concat(n.author_name_family ), group_concat(DISTINCT d.divisions)
FROM item e
LEFT OUTER JOIN divisions d on (e.itemid = d.itemid)
LEFT JOIN item_authors_name n on (e.itemtid = n.itemid)
GROUP BY e.itemid
HAVING COUNT(n.itemid) > 1
ORDER BY authornum;
I wanted a list of items that had more than one author. auhors, in a seperate table, are linked to the item: one to many. So I used a group by, and COUNT to get the number of linked authors for each item.
The next bit got me. Turns out you can’t use a COUNT in the WHERE clause, so while I could simple remove items with one author by hand in the final output, I really wanted to do this here. Turns out this is where the HAVING command comes in. I had come across this years a go but discarded it as just another way to do what the WHERE clause.
I also wanted to include the author names in the output. There would be multiple authors per item (as per above) but I wanted them all in one field, seperated by commas. for mysql, group_concat does the job. You need to use it with a group by clause.
Finally, embarrasingly, I had forgotten my joins. I wanted to list the departments (divisions) each item was linked to in our organisation, and wanted the output to be similar to the authornames: one field, seperated by commas. Again group_concat came in useful. However not all items are linked to departments, and these were orginally now being missed out of the report (it was critical we included all). Of course, what I wanted was a LEFT OUTER JOIN. This ensures we get all items, and if an item has no divisions then the latter will just be NULL.
UPDATE (3 minutes after posting this): turns out, once again, I had not only already discovered one of these solutions, but also bloody blogged about it.