Results 1 to 4 of 4
  1. #1
    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. #2
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    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. #4
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •