Multiple Outer Joins

[Chris Fisher-2]

Hello All -

Just a brief question. I am trying to help someone figure out why their SPSS
is not allowing them to join more than 2 tables in a database system when
they have multiple left or right outer joins.

They can join numerous tables if all the joins are inner joins, but once
they put in more than one outer join, SPSS says it cannot join more than two
tables.

I'm an SPSS-fanatic but database communication technology makes no sense to
me, so any help would be greatly appreciated.

Thanks!

--
Christopher Fisher, Ph.D.
Adjunct Professor
John Jay College of Criminal Justice
555 West 57th Street, Suite 605
New York, New York 10019
212-237-8000 (x2145)
212-237-8644 (fax)

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Hal 9000]

Chris,

Using the graphic query builder in MSAccess and probably SPSS it only
allows a single outer join to be created. If you want multiple outer
joins, you'll have to edit the SQL statement in the get data command.
It should look something like this:

SELECT a.f_name, b.car, c.city
from (T1 a left outer join T2 b on b.id = a.id) left outer join T3 c on
c.id = a.id;

Using These Tables:

(T1)

id      f_name  l_name
----------------------------------
1       bill        mips
2       sally     well
3       jim       swell
4       kelly     ells
5                   doe
6       will
7       pam     black
8       mike    white
9       alice     pace

(T2)

id      car
--------------------
1       dodge
2       ford
3       toyota
5       honda
6       saturn
8       hyundai

(T3)

id      city
----------------------------
1       Vancouver
2       San Francisco
3       Bakersfield
5       Reno
6       Portland
7       Richmond
9       Alameda


The Resulting Query Yields:

f_name   car          city
-------------------------------------------------
bill         dodge      Vancouver
sally      ford          San Francisco
jim         toyota      Bakersfield
kelly
            honda       Reno
will        saturn      Portland
pam                      Richmond
mike     hyundai
alice                      Alameda


(Example done in MS Access)....Good Luck!
-Gary

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD