HELP with a Left Join, maybe I need a double Join??

[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.

 

[MeanGreen]

Instead of Left Join use LEFT OUTER JOIN.

Good Luck.

 

[malexanian]

Outer is optional if you already supply Left.

You need a cross join. A cross join of image and cat will get you a record for every possible combination of image and cat. Then you left join that onto image_cat to pull the third field in:

SELECT i.imageID, c.catID, ic.image_catID
FROM
image i
CROSS JOIN
cat c
LEFT JOIN
image_cat ic
ON i.imageID = ic.imageID AND
c.catID = ic.catID