Luis Carlos Junges wrote:

> 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."
>

Do you have a maximum level if 3 ? if so, you could just make left joins
with the same table.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br