Results 1 to 4 of 4

053011, 11:13 #1Registered User
 Join Date
 May 2011
 Posts
 2
A bit of relational algebra needed...
I often run a query which selects records from a fourkey 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?

053011, 14:59 #2Resident 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
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

060311, 05:07 #3Registered 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 sightcheck each run (e.g. to sightcheck 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.

060311, 14:35 #4Resident 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
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.