Terry A. Haimann wrote:
Lets say I have a Database defined as
create table Photos (
PhotoIdx Varchar(20),
PhotograherIdx varchar(20),
PhotoTypeIdx varchar(20),
CameraIdx varchar(20)
PhotoDate DateTime );
Create table Photographer (
PhotographerIdx varchar(20),
Photographer varchar(50));
Create table PhotoType (
PhotoTypeIdx varchar(20),
PhotoType varchar(50));
Create table Camera (
CameraIdx varchar(20),
Camera varchar(50));
Therefore, a select to print the basic info would be:
select Photos.PhotoIdx, Photographer.Photograper, PhotoType.PhotoType,
Camera.Camera, Photos.PhotoDate from
Photos, Photorapher, PhotoType, Camera where
((Photos.PhotograherIdx = Photographer.PhotograperIdx) and
(Photos.PhotoTypeIdx = PhotoType.PhotoType) and
(Photos.CameraIdx = Camera.CameraIdx));
A lot of typos in that, but I'll assume that wasn't the problem.
The problem with doing this is that if any of the lookup indexes are
null (such as Photos.PhotographerIdx, Photos.PhotoTypeIdx or
Photos.CameraIdx) or point to a nonexistent record in the lookup table,
the record will not be selected. So one has to use a left outer join.
The problem is I can get a left outer join to work that links two table,
but not more.
It's hard to say what you did wrong without seeing your query.
So how woulds you construct the above select to use only left outer
joins???
Thx, Terry
This should do:
SELECT Photos.PhotoIdx, Photographer.Photographer, PhotoType.PhotoType,
Camera.Camera, Photos.PhotoDate
FROM Photos
LEFT JOIN Photographer
ON Photos.PhotographerIdx = Photographer.PhotographerIdx
LEFT JOIN PhotoType
ON Photos.PhotoTypeIdx = PhotoType.PhotoType
LEFT JOIN Camera
ON Photos.CameraIdx = Camera.CameraIdx;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
