Re: Problem with SQL :

Paolo wrote:

> I've 2 tables : TABELLA_A , TABELLA_B as below
>
> ____________________________
> | FIELDA1 | KEYA | FIELDA2 | TABELLA_A
> |_________|_______|__________|
> | | | |
> | ASTERIX | AX | MZ |
> |---------|-------|----------|
> | OBELIX | AX | SZ |
> |---------|-------|----------|
> | TERLIX | AX | PZ |
> |_________|_______|__________|
>
>
>
>
> _______________________________
> | FIELDB1 | KEYB1 | FIELDB2 | TABELLA_B
> |_________|_______|_____________|
> | | | |
> | ASTERIX | AX | CL |
> |---------|-------|-------------|
> | ASTERIX | AX | PI |
> |---------|-------|-------------|
> | ASTERIX | AX | RE |
> |_________|_______|_____________|
> | | | |
> | OBELIX | AX | CL |
> |_________|_______|_____________|
> | | | |
> | OBELIX | AX | JE |
> |_________|_______|_____________|
> | | | |
> | OBELIX | AX | PI |
> |_________|_______|_____________|
>
>
> If I execute the query :
>
> SELECT FIELDA1,FIELDB2 FROM TABELLA_A,TABELLA_B
> WHERE TABELLA_A.KEYA = 'AX'
> AND TABELLA_A.FIELDA2 = 'MZ'
> AND TABELLA_A.KEYA = TABELLA_B.KEYB1
> AND TABELLA_A.FIELDA1 = TABELLA_B.FIELDB1
>
> I obtain this result:
>
> _____________________
> | FIELDA1 |FIELDB2 |
> |_________|___________|
> | | |
> | ASTERIX | CL |
> |---------|-----------|
> | ASTERIX | PI |
> |---------|-----------|
> | ASTERIX | RE |
> |_________|___________|
>
> but my task is to obtain only one record as below:
>
>
> ________________________________
> | FIELDA1 | FIELDB2 |
> |_________|______________________|
> | | |
> | ASTERIX | CL;PI;RE |
> |_________|______________________|
>
> Is is possible with a specific query? Someone can help me?
> Thanks in advance.

One of two choices. Either you table design is bad or you will need to write a function or procedure.

My choice would be a proc with a cursor loop.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
(remove one 'x' from my email address to reply)