Solved

Nested Outer Joins

Posted on 2003-10-27
5
906 Views
Last Modified: 2008-02-26
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
Comment
Question by:see201
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
Dexstar earned 125 total points
ID: 9629991
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
 
LVL 2

Author Comment

by:see201
ID: 9630003
Oh, I'm acessing DB/2 if that makes any difference.
0
 
LVL 2

Author Comment

by:see201
ID: 9630054
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9630058
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9634603
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question