• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 937
  • Last Modified:

Nested Outer Joins

Hi!

Hi have three tables as follows:

Table 1
---------

UID     T1ID     Data1
----     ------    -------
 1          1        xxxx
 1          2        yyyy
 2          3        zzzz
 2          4        aaaa


Table 2
---------

T1ID     Data2
-----     -------
  1        mmm
  3         nnnn


Table 3
---------

T1ID     Data3
-----     -------
  2         rrrrr
  4         ssss

What I want is to use one and only one query using UID=1 (for example) to get the following results:

Data1     Data2     Data3
-------    -------     -------
xxxx        mmm     NULL
yyyy        NULL      rrrrr


I've tried the following, but it doesn't work the way I want it to:

SELECT A.Data1, B.Data2, C.Data3
from Table1 AS A FULL OUTER JOIN
(Table2 AS B FULL OUTER JOIN Table3 AS C ON B.T1ID = C.T1ID) ON A.T1ID = C.T1ID AND A.T1ID = B.T1ID
where A.UID = 1

It gives me an error if I change the 'AND' in the from clause to an 'OR'.

Can anyone tell me what I'm doing wrong?
0
see201
Asked:
see201
  • 3
  • 2
1 Solution
 
DexstarCommented:
see201:

> Can anyone tell me what I'm doing wrong?

Try this:

     SELECT
          A.Data1, B.Data2, C.Data3
     FROM
          Table1 A
       LEFT OUTER JOIN Table2 B ON
          A.T1ID = B.T1ID
       LEFT OUTER JOIN Table3 C ON
          A.T1ID = C.T1ID
     WHERE
          A.UID = 1

If that doesn't work, post the errors/problems you get.

Hope that helps,
Dex*
0
 
see201Author Commented:
Oh, I'm acessing DB/2 if that makes any difference.
0
 
see201Author Commented:
Hi Dexstar,

Thanks!  That worked perfectly.  I thought I couldn't use Left outer joins because I wanted to get null values both ways, but somehow that worked.

Kudos!
0
 
DexstarCommented:
Not sure if it does make a difference or not.  Try the SQL Statement I gave you, post any problems, and we'll go from there.

Dex*
0
 
DexstarCommented:
see201:

You would use FULL OUTER joins if you had NULL values in Table1 that you also wanted to include.  However, there aren't NULLs in Table1.  So in your case, you don't really want NULL values "both" ways, you want it one way but for 2 different tables.  A subtle but important distinction.

Hope that helps,
Dex*
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now