Home » SQL & PL/SQL » SQL & PL/SQL » Avoiding multiple rows
Avoiding multiple rows [message #207710] Wed, 06 December 2006 13:19 Go to next message
adieu57
Messages: 2
Registered: December 2006
Junior Member
Hi I have a query involving 4 tables.

t1 joins to t3, t2 joins to t4 (outer join on t4) and t2 joins to t3.

I am returning fields from all t1,t2 and t4.

t1 has unique key values linked to unique values in t3.
t2 has unique values linked to multiple values in t4.

I want to avoid bringing back multiple rows from t4 for each value that results in the join from t1 to t3 but does not have a value in t4.

The query looks like this (simplified field names)
SELECT
t1.field1, t1.field2 ....,t2.field2.t2.field2 ..., t4.field1,t4.field2 ...
FROM t1,t2,t3,t4EMA_MIS.TBL_DWP_PARTC_UNQUAL_GARY, apl_application_lk,stl_student_lk,ACX_APL_CBL_XREF
where t2.x = t3.x and t3.x = t1.x and t2.x = t4.x (+)

When there are no matching rows in t4 for t3 the t4 fields are still returned, fine because of the outer join, but they have values in them and multiples of the same row are being returned with differing t4 values !!


Any help appreciated
Re: Avoiding multiple rows [message #207715 is a reply to message #207710] Wed, 06 December 2006 13:42 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Maybe you should give us more detailed information, because what you describe now just works, I think:

SQL> select * from t1;

COL1
----
a
b
c
d

SQL> select * from t2;

COL2
----
a
b
c
d
e

SQL> select * from t3;

COL3
----
a
b
c
e

SQL> select * from t4;

COL2 COL4
---- ----
a    a1
a    a2
a    a3
b    b1
b    b2
b    b3

6 rows selected

SQL> 
SQL> SELECT t1.col1
  2        ,t3.col3
  3        ,t2.col2
  4        ,t4.col4
  5    FROM t1
  6        ,t2
  7        ,t3
  8        ,t4
  9   WHERE t1.col1 = t3.col3
 10     AND t2.col2 = t3.col3
 11     AND t2.col2 = t4.col2(+);

COL1 COL3 COL2 COL4
---- ---- ---- ----
a    a    a    a1
a    a    a    a2
a    a    a    a3
b    b    b    b1
b    b    b    b2
b    b    b    b3
c    c    c    

7 rows selected


So, no values from t4 if no corresponding records exist in t2...
Re: Avoiding multiple rows [message #207854 is a reply to message #207710] Thu, 07 December 2006 03:48 Go to previous message
adieu57
Messages: 2
Registered: December 2006
Junior Member
Hi,

Thanks for the reply.

There are only additional criteria that relate to equality on fields on t3 and t2 ie t3.fieldx = 'VALUE and t2.fieldy = 'VALUE'.

I've run it again and checking t4 it seems like there is a problem with it,in that there are multiple values for each row in t2 when there shouldn't be !

So my sql was kind of correct but the data is dodgy. Will now be checking further into the anomaly.

Thanks for clarifiication anyway
Previous Topic: Overcoming Mutating Table Error
Next Topic: Distinguish between global variables and private variable
Goto Forum:
  


Current Time: Sat Dec 10 03:05:13 CST 2016

Total time taken to generate the page: 0.08686 seconds