Hey,
I have one doubt about how to build a recursive SQL statement


I have a table like this:

CREATE
TABLE TBCREFERENCEUNLINKED(CPN VARCHAR(30), CNAME VARCHAR(30),
SALESCOMMENTS BLOB SUB_TYPE TEXT, CPN1 VARCHAR(30), CNAME1 VARCHAR(30),
SALESCOMMENTS1 BLOB SUB_TYPE TEXT,PRIMARY KEY(CPN,CNAME,CPN1,CNAME1) );

cpn = first company part
cname = first company name
cpn1 = second company part
cname1= second company name

I need to group all entries according to Company Name (cname ou cname1) given by user

Example:
For a table with the following data:

cpn | cname | cpn1 | cname1
A | C1 | C | C2
D | C4 | A | C1
F | C6 | E | C5
A | C1 | F | C6
C | C3 | B | C2


The user chooseCOMPANY NAME like 'C1'
(I select all PART NUMBERS which COMPANY NAME is 'C1')

SELECT
DISTINCT T1.CPN AS PartNumber, T1.CNAME FROM TBCREFERENCEUNLINKED T1
WHERE T1.CNAME='C1' UNION SELECT DISTINCT T2.CPN1, T2.CNAME1 FROM
TBCREFERENCEUNLINKED T2 WHERE CNAME1='C1';

Answer:
A | C1
K | C1


Now i need see all others refcerences for each part number retrieved above(A e K):
Only for A here:
SELECT
DISTINCT T1.CPN AS PartNumber, T1.CNAME1 AS Company FROM
TBCREFERENCEUNLINKED T1 WHERE T1.CNAME='C1' AND T1.CPN='A' UNION SELECT
DISTINCT T2.CPN,T2.CNAME FROM TBCREFERENCEUNLINKED T2 WHERE
T2.CNAME1='C1' AND T2.CPN1='A'

Answer:
C | C2
D | C4
F | C6

Well, now i need to know how to retrieve others number which are recursive.The final answer for COMPANY
NAME='C1' and PART NUMBER='A' should be:

Answer:
C | C3
D | C4
F | C6
B | C2
E | C5



Any help of how to it with SQL statement?


Best Regards,




---
Luís Carlos Dill Junges ©

"A realidade de cada lugar e de cada época é uma alucinação coletiva."
Bloom, Howard


Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @... ou @....
http://br.new.mail.yahoo.com/addresses

[Non-text portions of this message have been removed]