Re: SQL Challenge

Minh Van Le wrote:
>> The problem: I have a scenario where I have a table T1 with say 3 fields
>> F1, F2 and F3. From this table I need to select all rows whose F1 are in
>> a specific subset, as well as any rows from the matched rows so far
>> whose F2 fields are the same, as well as any rows from the matched rows
>> so far whose F3 fields are the same. Make sense? :)
> 
> I don't understand what you're trying to do.
> 
> I got as far as
> 
>   "From this table I need to select all rows whose F1 are in a specific
> subset," ...
> 
> When you say
> 
>   "as well as any rows from the matched rows"
> 
> do you mean
> 
>   "as well as any columns from the matched rows" ?
> 

Sorry, typed in a hurry with no proofing...it should've read "as well as
any rows whose F2 field is the same as F2 from the matched rows"

>> So, for example:
>> Table T1
>> F1  F2  F3
>> ----------
>> 1   1   3
>> 1   1   4
>> 1   3   4
>> 1   4   3
>> 1   8   9
>> 2   5   3
>> 2   6   7
>>
>> Given an input of say F1=1 and R1=1, how would one go about obtaining
>> the following result set:
>> F1  F2  F3
>> ----------
>> 1   1   3
>> 1   1   4
>> 1   3   4
>> 1   4   3
> 
> What is "R1" ? Row 1 ?
> 

It should be F2 - again hurried with no proofing.

>> Or with an input of say F1 in (1,2) and R1 in (1,5)
>> F1  F2  F3
>> ----------
>> 1   1   3
>> 1   1   4
>> 1   3   4
>> 1   4   3
>> 2   5   3
> 
> I don't even know how you got the above result with "F1 in (1,2) and R1 in
> (1,5)" because I don't know what R1 means.
> 
> You sound like you need self joins or correlated subqueries.

Irrespective of mistyping and so on, I've come to the conclusion that
recursion is in fact the only way, so was heading down the path of sql
procedure (I have in fact, for other reasons, decided to take a few
steps further back and change the structure of the data so as to avoid
having to do these lookups all together).

Thanks for everyones input
Fil
-- 
SLUG - Sydney Linux User Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html