Hello,
I am joing 4 tables to retrieve data from an oracle database using Perl DBI.
Once I recieve the data, I put it in xml format. Table A has 1 to many
relationship with table B, C, and D. Table D has one to many releationship
with table B and C. The problem is that every record I recieve I get a lot
of duplicate information and I can not use distinct since I am joining
multiple tables. The following is the select statement:
Select
A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4,B.r_n
me,B.str1,B.str2,B.city,B.state,B.zip,C.stat,C.y_date,C.x_date,D.p_num,D.a_n
um from tableA A, tableB B, tableC C, tableD D
where A.b_code = '6'
and (B.FK_R_id = A.R_id and B.FK_F_id = A.F_id)
and (C.FK_R_id = A.R_id and C.FK_F_id = A.F_id)
and (D.FK_R_id = A.R_id and D.FK_F_id = A.F_id)
and D.p_num IS NOT NULL
I will get thousands of records with duplicate information. Each record
returned is unique, however
A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4 is
repeated for each B.r_nme or each D.p_num,D.a_num etc or
A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4,B.r_n
me,B.str1,B.str2,B.city,B.state,B.zip,C.stat,C.y_date,C.x_date is repeated
for each D.p_num...
How can I approach this problem so that for each record returned I ignore
the duplicate information and get take the unique informaton for that A.R_id
and A.F_id and take that information and put it in xml format?
Any help is appreciated....
Thanks!!
Sumera
