?
Solved

Nested Outer Joins

Posted on 2003-10-27
5
Medium Priority
?
915 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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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