Even though I have only used one WHERE clause in a query (I don’t really do subqueries or anything like that; it’s easier for me to do complicated things in separate queries and then just combine them in R), SQL queries always remind me of the recital at the beginning of a legal document. WHEREAS, the first party… WHEREAS, the second party…

At the end of the day yesterday I realized that I could take something that I was stupidly doing with two queries and fairly easily combine them into one by using AND and NOT and a lot of parentheses (and putting one more column in the SELECT). But it turned out not to be “fairly easy,” (was it a copy-paste error? An errant NOT somewhere? An inequality going the wrong direction? Maybe when I added that extra column to the SELECT, I accidentally wrote the name of a different column? These are all mistakes that I make), and now I am seriously considering making a truth table today to figure out why my query is not working.

If I were making any meaningful pretence at shamelessly promoting my technical skills, instead of telling you about the errors I usually make, I would have a short tutorial here about DeMorgan’s laws. But no one wants that.

(Also, despite my usual disdain for IDEs, I probably do need a spell-checker in my editor that will tell me when I type something in a variable-like grammatical context that is not actually the name of one of my columns or variables.)

(And, yes, I also type these blog entries in an editor with no spell-checker.)

When I grew up in New York State, truth tables and propositional logic were a required part of the high school math curriculum. On one of the Regents exams, you could choose between doing the logic proof and the geometry proof. Who would pick the geometry proof!? (Since I now work with some of the world’s leading experts on high school geometry, my lack of interest in geometry is not a problem. Any question that requires more than knowing that the measure of the inscribed angle is half the measure of the interecpted arc can be delegated to a real expert.)

But clearly this is not the case everywhere. The last R-Ladies Meet-up that I was at, someone asked a question indicating that she hadn’t had formal training in combining logical predicates. I don’t think that any of our middle school or high school level courses have any lessons on formal logic. Maybe some of our intermediate classes have an aside or two about “for all” and “there exists”? But certainly not much. I think that we have some logic games in our elementary school curriculum, but I don’t imagine that we have 4th-graders intoning (p implies q) and p, therefore q. Other curricula might be a mile wide and an inch deep; ours is remarkably lean and efficient.

I’m hoping that I’ve cleared enough off my stack that I can finish working on this (now moot) task from last week. Last week I was able to put together a “good enough” view of the situation, but with some noise from the special cases. This week things were somewhat better because a saved spreadsheet got old enough and a synced copy of the database got new enough that the spreadsheet didn’t know about a future that the database copy was unaware of. And the questions that I’m looking at will come up again, so it’s worth finally getting this code working correctly so that I can do simple modifications the next time this question comes up and give an answer that is both true (as, even with the special cases not quite right, the conclusion was correct) and accurate.