Hello
Assume that I have the following three 3 tables:
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)
);
The photo_album tables defines an album which has a cateogry ID. Information about the different categories can be found at the album_categories. Each album has information about its publication and its stored in the publishable_item table.
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:
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;
This query returns correct information ONLY for the albums whose submitter is 1.
It doesn't list the categories that submitter 1 isn't the owner of their albums
I hope this is clear
