[SOLVED] problem in getting the right data from a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. :)
Last edited by feyd on Tue Jul 20, 2004 2:04 pm, edited 1 time in total.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

nice =)
ultraslacker
Forum Newbie
Posts: 3
Joined: Tue Jul 20, 2004 2:11 pm

Post 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.
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

cool, thanks for the help, that solved it
Post Reply