Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Nested Outer Joins

Posted on 2003-10-27
5
Medium Priority
?
923 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 ClickHouse performs in a general analytical workload using the star schema benchmark test.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

598 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