SQL Question - multiple outer joins

Can anyone advise how to do the following:

I have two tables. Table A contains  

         ID NOT NULL and table B

     
         TAB_A   NOT NULL /* foreign key to table A */
         TYPE
         VALUE

where there is a one to many between A and B. Type can be X or Y, and each A can have 0 to 2 corresponding records.

I want to create a view

          ID
          XVALUE 
          YVALUE

for all records on A. The obvious

select A.ID, B1.VALUE, B2.VALUE
  from A, B B1, B B2
 where A.ID = B1.ID (+)
   and B1.TYPE = 'X'
   and A.ID = B2.ID (+)
   and B2.TYPE = 'Y';

doesn't work because of the multiple outer joins.

Can I do it any other way? Received on Wed Jan 19 1994 - 15:49:21 CET