Assume that I have the following three 3 tables:
Code: Select all
CREATE TABLE їb]publishable_itemї/b] (
Item_ID INT UNSIGNED NOT NULL,
Submitter_ID INT UNSIGNED NOT NULL,
PRIMARY KEY (Item_ID)
);
CREATE TABLE їb]album_categoriesї/b] (
Category_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Title VARCHAR(255) NOT NULL,
PRIMARY KEY (Category_ID)
);
CREATE TABLE їb]photo_albumї/b] (
Album_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Category_ID INT UNSIGNED NOT NULL,
PRIMARY KEY (Album_ID)
);I would like to extract all the info on the categories along with the number of albums that use the category for each category.
All this should be extracted for the albums whose submitter has ID 1.
Basically, if an album was submitted by submitter 1, then it should be counted in the number of albums that belong to a category, otherwise don't count it.
here is my initial attempt:
Code: Select all
SELECT album_categories.*,
COUNT(photo_album.Album_ID)
FROM album_categories
LEFT JOIN photo_album ON album_categories.Category_ID = photo_album.Category_ID
INNER JOIN publishable_item ON publishable_item.Item_ID = photo_album.Album_ID AND publishable_item.Submitter_ID = 1
GROUP BY Category_ID;It doesn't list the categories that submitter 1 isn't the owner of their albums
I hope this is clear
help would be appreciated