Home » SQL & PL/SQL » SQL & PL/SQL » Single row result from multiple sub tables (Many tables join) (Oracle 10g)
Single row result from multiple sub tables (Many tables join) [message #378024] Sat, 27 December 2008 18:02 Go to next message
hq4ever
Messages: 5
Registered: November 2008
Junior Member
Hello,

I'm trying to merge results from several related tables into a single row result where results from the subtables should be in CSV fomrat. I'm having 2 problems:

1. I'm unable to created a workable join expression that would always return results from the main table and complement them with results from the subtables.
2. Results in the CSV column return as a Cartesian product which is not the expected result and I am not clear as for the reason of this.


I have subscriber data in a table and complimentary data in sub tables, test case attached below.

I would like to receive output in this format :

| subscribers.objectkey | Name  | locationsCollection | interestsCollection | 
|                     1 | Maxim | Loc1,Loc2,Loc3      | Int1, Int2, Int3 | 


I've tried something like the following, which does not produce the right result.

SELECT 
    subscribers.OBJECTKEY, subscribers.name
    , wmsys.wm_concat(subscribers_locations.location_) locationCollection
    , wmsys.wm_concat(subscribers_interests.interest) interestsCollection
FROM
   subscribers, subscribers_locations, subscribers_interests
WHERE
   subscribers.objectkey = subscribers_locations.parentkey and 
   subscribers_locations.parentkey = subscribers_interests.parentkey
GROUP BY subscribers.OBJECTKEY, subscribers.name
;




Test case :
create table subscribers (
  objectkey     number(10),
  name  varchar2(50)
);

create table subscribers_locations (
  objectkey     number(10),
  parentkey     number(10),
  location_   varchar2(100)
);

create table subscribers_interests (
  objectkey     number(10),
  parentkey     number(10),
  interest   varchar2(100)
);

insert into subscribers values (1, 'Maxim');
insert into subscribers values (2, 'FooBar');

insert into subscribers_locations values (1, 1, 'Loc1');
insert into subscribers_locations values (2, 1, 'Loc2');
insert into subscribers_locations values (3, 1, 'Loc3');
insert into subscribers_locations values (4, 2, 'Loc2');
insert into subscribers_locations values (5, 2, 'Loc7');

insert into subscribers_interests values (1, 1, 'Int1');
insert into subscribers_interests values (2, 1, 'Int2');
insert into subscribers_interests values (3, 1, 'Int3');
insert into subscribers_interests values (4, 2, 'Int2');
insert into subscribers_interests values (5, 2, 'Int7');



Help on this issue would be highly appreciated.
Re: Single row result from multiple sub tables (Many tables join) [message #378027 is a reply to message #378024] Sat, 27 December 2008 21:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following produces the desired results, but you should be aware that wm_concat is an undocumented function and there are other documented methods. If this is a one time thing, usage of wm_concat is fine, but if you plan to use it in an application then you should be aware that undocumented functions are unsupported and there is no guarantee that such functions will be available in future versions or that they do not have bugs which may produce unpredictable results.

SCOTT@orcl_11g> create table subscribers (
  2    objectkey     number(10),
  3    name  varchar2(50)
  4  );

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> create table subscribers_locations (
  2    objectkey     number(10),
  3    parentkey     number(10),
  4    location_   varchar2(100)
  5  );

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> create table subscribers_interests (
  2    objectkey     number(10),
  3    parentkey     number(10),
  4    interest   varchar2(100)
  5  );

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> begin
  2    insert into subscribers values (1, 'Maxim');
  3    insert into subscribers values (2, 'FooBar');
  4  
  5    insert into subscribers_locations values (1, 1, 'Loc1');
  6    insert into subscribers_locations values (2, 1, 'Loc2');
  7    insert into subscribers_locations values (3, 1, 'Loc3');
  8    insert into subscribers_locations values (4, 2, 'Loc2');
  9    insert into subscribers_locations values (5, 2, 'Loc7');
 10  
 11    insert into subscribers_interests values (1, 1, 'Int1');
 12    insert into subscribers_interests values (2, 1, 'Int2');
 13    insert into subscribers_interests values (3, 1, 'Int3');
 14    insert into subscribers_interests values (4, 2, 'Int2');
 15    insert into subscribers_interests values (5, 2, 'Int7');
 16  end;
 17  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN name		   FORMAT A6
SCOTT@orcl_11g> COLUMN locationsCollection FORMAT A30
SCOTT@orcl_11g> COLUMN interestsCollection FORMAT A30
SCOTT@orcl_11g> SELECT subscribers.objectkey,
  2  	    subscribers.name,
  3  	    (SELECT wm_concat (subscribers_locations.location_)
  4  	     FROM   subscribers_locations
  5  	     WHERE  subscribers_locations.parentkey = subscribers.objectkey)
  6  	     AS locationsCollection,
  7  	    (SELECT wm_concat (subscribers_interests.interest)
  8  	     FROM   subscribers_interests
  9  	     WHERE  subscribers_interests.parentkey = subscribers.objectkey)
 10  	     AS interestsCollection
 11  FROM   subscribers
 12  /

 OBJECTKEY NAME   LOCATIONSCOLLECTION            INTERESTSCOLLECTION
---------- ------ ------------------------------ ------------------------------
         1 Maxim  Loc1,Loc2,Loc3                 Int1,Int2,Int3
         2 FooBar Loc2,Loc7                      Int2,Int7

SCOTT@orcl_11g>

Re: Single row result from multiple sub tables (Many tables join) [message #378051 is a reply to message #378024] Sun, 28 December 2008 03:40 Go to previous messageGo to next message
hq4ever
Messages: 5
Registered: November 2008
Junior Member
Thank you very much Barbara your solution works great. Can you please explain what the format A30 means ?
Re: Single row result from multiple sub tables (Many tables join) [message #378056 is a reply to message #378051] Sun, 28 December 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12013.htm#i2697128

Regards
Michel
Re: Single row result from multiple sub tables (Many tables join) [message #378076 is a reply to message #378056] Sun, 28 December 2008 06:13 Go to previous message
hq4ever
Messages: 5
Registered: November 2008
Junior Member
I see, so it's just a display key and does not need to appear in my view.


That sums it, thank you very much you have helped me alot.
Previous Topic: password
Next Topic: UTL_HTTP,UTL.DBWS or Table Functions?
Goto Forum:
  


Current Time: Sun Dec 11 00:18:57 CST 2016

Total time taken to generate the page: 0.04355 seconds