Re: Problem with SQL :

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 25 Apr 2003 10:28:26 -0700
Message-ID: <336da121.0304250928.4158dc0e@posting.google.com>

pablofrompadova_at_yahoo.it (Paolo) wrote in message news:<a1041494.0304240132.378c2c84_at_posting.google.com>...
> 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.

There are two possible answers.

The design is bad and you need to change design.
You can do it using creepy combination of decode and group functions:

 SELECT FIELDA1, max(decode(b.rr, 1, b.FIELDB2 || ';', null)) ||
                 max(decode(b.rr, 2, b.FIELDB2 || ';', null)) ||
                 max(decode(b.rr, 3, b.FILEDB2, null))
........................................................

FROM TABELLA_A,
(select rownum rr, FIELDB2, KEYB1, FIELDB1 from TABELLA_B ) b

     WHERE TABELLA_A.KEYA = 'AX' 
        AND TABELLA_A.FIELDA2 = 'MZ'
        AND TABELLA_A.KEYA = b.KEYB1  
        AND TABELLA_A.FIELDA1 = b.FIELDB1

GROUP BY FIELDA1 Keep in mind that:

The example above is for 3 values from TABELLA_B per FIELDA1 maximum. If you might have more values, add "max(decode" functions. The number of rows in TABELLA_B you can process this way for every FIELDA1 is restricted by number of "max(decode" functions in select clause.

HTH Received on Fri Apr 25 2003 - 12:28:26 CDT