see201
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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*
Dex*
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*
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*
ASKER