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