Well, you are probably wondering what prompted my last post about ‘sql ghettos’. And well you might. The incident that spawned the term happened over a year ago.
But, I thought I should get my personal stand on the matter documented before I offer this little tid-bit of sql twistedness.
In a current application I am working on, I need to produce a list of items for which work has NOT been done.
Work being done is recorded against the item by attaching a tag with the name of the work task.
Gathering a list of items to be worked on next therefore means looking for items which do NOT have specific tag values associated with them.
Previously, it was good enough to find items for which not all the tags where defined and then spit out a list of the items. That was fine, and handled by a simple piece of code that just counted up how many tags of specific names each item had, and if it had less than the number I was looking for, include it in the list. Fine, jobs a good un, put your feet up, have a cuppa.
But NOW, I need to start keeping track of which tasks have been released to which systems running the jobs.
In order to do this, I need the end result to be a list of items, and where that item does not have a tag, I want a ROW returned with the id of the item and the tag THAT IS NOT THERE.
This is a vastly different argument, and requires you to start searching not just for a low count, but searching AND FINDING things that are not there.
This can be split up into two steps…
- generating a list of the items crossed by the tags you are looking for.
- decimating that list on finding the tag present.
The first step sounds simple, until you remember that the tag might be referencing a totally new piece of work that has never been done before, and so will not be resident anywhere in the tag table.
So, what you are asking to do is to be able to ‘cross’ an sql table with an array of string literals.
Now, stop, go away and try to do it.
I will discuss my solution after the break…
For instance, if your literals are [‘a’, ‘b’, ‘c’]
you can use…
select i.id, coalesce(c, b, a) as d
from items i,
(select 'a' a) a,
(select 'b' b) b,
(select 'c' c) c
group by i.id, a, b, c with rollup
having not d is null;
This does its magic by means of the ‘with rollup’ and ‘coalesce’
getting rid of the ancilliary bits with the ‘having’ is almost
an afterthought.
And yes, I know, this is an sql ghetto of the deepest, darkest, most dangerous kind.
Having said that, isn’t it cute?
I have to go for a bit of a lie down now, for some strange reason, my ears are bleeding. (JOKE people, put down the phone!)
And please, if there is a neater and less twisted way to do it, please, do let me know.