Do two OUTER JOINS:

	SELECT * FROM User
		LEFT OUTER JOIN Address ON User.UserID=Address.UserID
		LEFT OUTER JOIN Country ON Address.CountryID=Country.CountryID;

Note that some brain-dead RDBMSs (like Access) won't allow multiple OUTER
JOINs; you'd have to construct a view (query) and then OUTER JOIN that...

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Joe Hughes [mailto:JoeHughes@M...]
Sent: Thursday, October 24, 2002 6:56 AM
To: sql language
Subject: [sql_language] Join Question


Hi,

Would someone mind giving me some help with this join;

If I have three tables;

=========
User
=========
UserID PK

=========
Address
=========
UserID (FK User.UserID)
Address1
Address2
Address3
CountryID (FK Country.CountryID) not null

=========
Country
=========
CountryID PK
CountryName

Its easy to do an INNER JOIN across the three tables... Each must have a
country, while a user doesnt have to have an entry in the address table.

I'm getting a little stuck when the User "Doesnt" have an entry in the
address table.. I've used a LEFT OUTER JOIN against Address from Users,
which returns NULL if there isnt an entry in Address which is fine. What I
want is the CountryName to be returned from Country when Address.CountryID
isnt null, but CountryName to be null when there isn't an entry in address.

This is where the question comes into it...

How do I join from Address->Country, When User->Address is a LEFT OUTER
JOIN, do i chain the join on the end of the initial join?

Any help is appreciated

Joe Hughes