Page 2 of 2

Posted: Tue Jul 20, 2004 1:32 pm
by davidklonski
insert into album_category (Category_ID, Title) values (1, 'cat1');
insert into album_category (Category_ID, Title) values (2, 'cat2');
insert into album_category (Category_ID, Title) values (3, 'cat3');
insert into album_category (Category_ID, Title) values (4, 'cat4');
insert into album_category (Category_ID, Title) values (5, 'cat5');

insert into photo_album (Album_ID, Category_ID) values (1, 1);
insert into photo_album (Album_ID, Category_ID) values (2, 2);
insert into photo_album (Album_ID, Category_ID) values (3, 1);
insert into photo_album (Album_ID, Category_ID) values (4, 3);
insert into photo_album (Album_ID, Category_ID) values (5, 1);

insert into photo publishable_item (Item_ID, Submitter_ID) values (1, 1);
insert into photo publishable_item (Item_ID, Submitter_ID) values (2, 2);
insert into photo publishable_item (Item_ID, Submitter_ID) values (3, 1);
insert into photo publishable_item (Item_ID, Submitter_ID) values (4, 1);
insert into photo publishable_item (Item_ID, Submitter_ID) values (5, 2);

when I would like to see the info on the categories for submitter 1 I should get:

Code: Select all

category_ID  Title  NumAlbums 
1            cat1   2 
2            cat2   0 
3            cat3   1 
4            cat4   0 
5            cat5   0
I hope this helps

Posted: Tue Jul 20, 2004 2:01 pm
by feyd

Code: Select all

SELECT a.* , COUNT( b.Item_ID ) NumAlbums
FROM `album_categories` a
LEFT  JOIN `photo_album` c ON c.Category_ID = a.Category_ID
LEFT  JOIN `publishable_item` b ON b.Item_ID = c.Album_ID AND b.Submitter_ID = 1
GROUP  BY a.Category_ID
The major thing I changed: COUNT from Album_ID (which was always included), to Item_ID, which would be included only when Submitter_ID was 1. :)

Posted: Tue Jul 20, 2004 2:02 pm
by liljester
nice =)

Posted: Tue Jul 20, 2004 2:11 pm
by ultraslacker
Nice work.

BTW, a count on anything in publishable_item would work, as all columns for the table will be null for the unmatched rows.

Posted: Tue Jul 20, 2004 2:12 pm
by davidklonski
cool, thanks for the help, that solved it