Re: SQL QUESTION (ADVANCED ??)

In article <3471jc$prm_at_crocus.csv.warwick.ac.uk> cudau_at_csv.warwick.ac.uk (Hank Robinson) writes:
>
>jos_at_zeus.wnc.nedlloyd.nl (Jos Kerssens) writes:
>
>> I have some problems writing a statement that selects the
>> required information in the following situation:
 

>> I have two tables that have an n:m relationship, with an
>> intersection table in between, as follows:
 

>> ------- ------- -------
>> | A |-------------<| B |>-------------| C |
>> ------- ------- -------
 

>> The relationships in table B could be like
 

>> A1-C1
>> A2-C2
>> A2-C3
>> A4-C4
>> A5-C4
>> A5-C5
>> A6-C5
>> A6-C6
>> A4-C6
 

>> Now, if I have a value for A, I want to select all other A's
>> that are somehow connected to the given value for A.
>> In case of A4, that would be A4, A5, A6.
>
>I see you're not interested in duplicate As, then; if you Were, then
>in the case of A4, that would be A4, A5, A6, A4.
>And I see that you don't mind the same A (eg. A4) being returned that you
>specify "in the where clause".
>Hokay...
>
>> The problem is that I don't know how many A's will be in such a
>> 'group', so the use of subselects gives problems.
>
>Thinking in terms of groups (Group By, I presume you mean) isn't going to
>help you with this one...
>
>> An other problem is that the relations can 'loop'. In above example
>> C6 points back to A4. A connect by clause will loop as well.
>
>Thinking in terms of Connect By isn't going to help you with this one,
>either...
>
>> The problem can be solved in PL/SQL with some tricks, but I really
>> need one SQL-statement that can do the trick.
>
>You don't need PL/SQL for this one; try this
>
>Select Distinct column_A
>From table_B X
>Where Exists (Select Null
> From table_B Y
> Where X.column_A = column_A And
> Exists (Select Null
> From table_B
> Where Y.column_C = column_C And
> column_A = '&value_A'))
>Order By column_A;
>
>Hank Robinson
>Oracle DBA
>University of Warwick

Would't the following suffice:

SELECT DISTINCT T1.a
FROM btest T1, btest T2
WHERE T1.c = T2.c and T2.a = '<a_value>' ORDER BY T1.a

It took me a while to figure out your formulation. Is it primarily motivated by efficiency concerns?

Larry Received on Fri Sep 02 1994 - 18:53:59 CEST