# Thread: A bit of relational algebra needed...

1. Registered User
Join Date
May 2011
Posts
2

## A bit of relational algebra needed...

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?

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

3. Registered User
Join Date
May 2011
Posts
2
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.

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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