Re: Marking a subset efficiently

nikolausrumm_at_hotmail.com (Nikolaus Rumm) wrote in message news:<e0fe462c.0304280235.532f3f87_at_posting.google.com>...
> Hello,
>
> I have a SELECT-Statement with a complicated WHERE-clause that is
> based on set operators (UNION, MINUS, INTERSECT).
> The purpose of this SELECT-Statement is to get a subset S1 of a set S.
> So far so usual.
> Unfortunately I want to get back the *whole* set (S) including those
> records that are part of the subset S1. The members of S1 should be
> marked with a 1-value in a flag.
>
> Example:
> S = {A1, A2, A3, A4}
> S1 = {A2, A3}
>
> I want to get all members of S, where A2 and A3 have
> FULFILL_CONDITION=1 and A1, A4 have FULFILL_CONDITION=0.
>
> Of course it's possible to partition the result using set operators
> but this is inefficient, as the complicated subselect will be executed
> several times. How should I do it (DECODE ?) ? Any help is
> appreciated.
>
> Regards, Nikolaus

Im assuming this is a college level database homework assignment since noone who does this for a living talks in terms of sets. You use the 'Database System Concepts' book. Seems popular at colleges. For some reason college professors feel the need to make SQL harder than it is by wording it in terms of set theory. Its far easier to learn SQL first, then sets later on if you are interested.

ok so in english:

Table A has 200 rows
Your where clause returns 50 rows.

However, you want all 200 rows, but you want the 50 returned by your where clause flagged with a 1 correct?

dont know how fast this will be... not going to tune it since its a homework assignment.

The easiest way to do it as follows. dont know if your professors wants this and its probably slow.

select columns, 1
from table
where blah, blah.. stuff you mentioned above union
select columns
from table
where != stuff above.

that is the easiest way. is that what the dopey teacher wants? Received on Mon Apr 28 2003 - 18:02:29 CEST