Re: SQL QUESTION (ADVANCED ??)

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'))

Or even

Select	a_whole_load_of_A_attributes
From	table_A X
Where	Exists (Select	Null
		From	table_B Y
		Where	X.column_A = column_A And
			Exists (Select	Null
				From	table_B Z
				Where	Y.column_A = column_A And
					Exists (Select	Null
						From	table_B
						Where	Z.column_C =
								column_C And
							  column_A =
								'&value_A')))

> Seems to me this is not such an extraordinary case so a solution
> must be around somewhere and might actually be simple.

Yep; you're right; quite common. And simple :-)

I use similar, but inverted, selects in some of my views for the following schema: we have a load of Members, some of whom can possess different memberShips (according to what they're doing; eg. staffship & studentship). Each memberShip can have any number of (within reason) Unix usercodes. Some of my views need to provide 'access privileges' to any Unix usercode as long as the Member, owning the Unix usercode, owns a 'privileged' Unix usercode.

> Thanks in advance,

You're welcome; hope this helps.

> Jos.
> --
> ------------------------------------------------------------------------
> Nedlloyd Computer Services _/ _/ _/_/_/_/ _/_/_/_/
> P.O. Box 2454 _/_/ _/ _/ _/
> 3000 CL Rotterdam (The Netherlands) _/ _/ _/ _/ _/_/_/_/
> J. Kerssens _/ _/_/ _/ _/
> _/ _/ _/_/_/_/ _/_/_/_/
> -----------------------------------------------------------------------

Hank Robinson
Oracle DBA
University of Warwick Received on Fri Sep 02 1994 - 13:17:00 CEST