LEFT OUTER JOIN PROBLEM?

[DwayneL]

Hello there,

I'm in need of some help with a left join.

Here are my tables.

One category can have many images and one images may belong to may categories.

category
(
name varchar(50) NOT NULL UNIQUE,
catID int(4) AUTO_INCREMENT PRIMARY KEY
);

image_category
(
fk_imageID int(8) NOT NULL REFERENCES images(imageID),
fk_catID int(4) NOT NULL REFERENCES category(catID),
image_catID int(10)AUTO_INCREMENT PRIMARY KEY
);

images
(
title_eng varchar(75) NOT NULL,
title_fre varchar(75) NULL,
description_eng text NULL,
description_fre text NULL,
access_num varchar(15) NOT NULL UNIQUE,
thumbnailImageName varchar(30) NULL,
detailImageName varchar(30) NULL,
imagedate varchar(15) NULL,
photographer varchar(30) NULL,
location varchar(25) NULL,
memo_note text NULL,
fk_copyright int(4) NULL REFERENCES copyright(copyrightID),
fk_collectionID int(4) NOT NULL REFERENCES collection(collectionID),
imageID int(8) AUTO_INCREMENT PRIMARY KEY
);

This was the query i was working with:
SELECT category.catID as catID , category.name as name, image_category.fk_imageID as imageID FROM category LEFT JOIN image_category
ON category.catID image_category.fk_catID
WHERE image_category.fk_imageID =3

This query only returns the check off ones and not all the cateogories along with the checked off information. I refer checked off as being accociated with a particluar image.

I want to get something like this for imageID 3

imageID
cat name

Books NULL
Dogs 3
Cats 3
People NULL
BALLs 3

I would appericate any help in this matter.

 

[adam0101]

Change your where clause to:
WHERE image_category.fk_imageID =3 OR image_category.fk_imageID IS NULL