A bit of relational algebra needed...

decomplexity · #1 (**permalink**) 05-30-11, 11:13

I often run a query which selects records from a four-key table when one or more of the keys has specific values, e.g. field A = “X” OR field B = “Y” and so on. Assume for brevity that each is either true or false, e.g. ‘A’ means the criterion is satisfied and ‘!A’ means that it is not.
My selection is thus: …WHERE A OR B OR C OR D.
So far so trivial.

For extraneous reasons, I was recently asked to perform the selections in four separate runs:
- all the As
- the Bs, except any records already selected in the A run
- the Cs, except any records already selected in the A and B runs
- the Ds, except any records already selected in the A, B or C runs

The corresponding selections would (I think!) be:
WHERE A

WHERE B AND !A

WHERE C AND !A AND !(B AND !A)

WHERE D AND !A AND !(B AND !A) AND !(C AND !A AND !(B AND !A))

I want to prove or disprove that the total records selected are identical with those selected in my usual way. Any offers please?

Pat Phelan · #2 (**permalink**) 05-30-11, 14:59

There are of course multiple ways to achieve this goal.

Disregarding the Relation Algebra part and going to "What works in the real world" which is more important to me, the answer is something akin to:

Code:

SELECT *
   FROM myTable
   ORDER BY CASE
         WHEN A then 1
         WHEN B then 2
         WHEN C then 3
         WHEN D then 4
      END

This uses the behavior of SQL to produce the results you want with a single pass through the data and it eliminates most of the pointless evaluations of the criteria.

-PatP

decomplexity · #3 (**permalink**) 06-03-11, 05:07

Thanks Pat.
My problem was that the user wanted four separate runs; the aim was to produce sticky labels for mailing, and the user concerned wanted to sight-check each run (e.g. to sight-check the B run to satisfy herself that it consisted of everyone who was a B apart from those who were also As).
So I need to prove that the outcome of the sensible way (WHERE A or B or C or D all in one pass) is identical to the logical union of the four sets produced by the four separate runs.

Pat Phelan · #4 (**permalink**) 06-03-11, 14:35

Show the column and order by it too, ie:

Code:

SELECT
   CASE
      WHEN A then 1
      WHEN B then 2
      WHEN C then 3
      WHEN D then 4
   END, *
   FROM myTable
   ORDER BY 1

The user can then visually confirm that the conditions are correct.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech