Page 1 of 2

problem in getting the right data from a query

Posted: Tue Jul 20, 2004 5:37 am
by davidklonski
Hello

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)
);
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:

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

help would be appreciated

Posted: Tue Jul 20, 2004 8:24 am
by liljester
Im not sure if itll fix your query or not, but try putting the submitter_ID in a WHERE instead of as part of your join... also, im surprised that query doesnt return an error, since (GROUP BY Category_id) Category_ID is ambigous?

Code: Select all

SELECT album_categories.*, COUNT(photo_album.Album_ID)
FROM album_categories
LEFT JOIN photo_album ON album_categories.Cattegory_ID = photo_album.Category_ID
INNER JOIN publishable_item ON publishable_item.Item_ID = photo_album.Album_ID
WHERE publishable_item.Submitter_ID = <put number here>
GROUP BY album_categories.Category_ID

Posted: Tue Jul 20, 2004 11:26 am
by davidklonski
I tried putting the condition on the submitter ID in a where clause but I am getting the same answer.

I am only getting the rows that match that submitter.
I would like to be able to get all the row, and in those rows in which the submitter submitted the album, to include the album in the count

I am not getting an error.
why is the (GROUP BY Category_id) Category_ID ambigous?

Posted: Tue Jul 20, 2004 11:33 am
by feyd
davidklonski wrote:why is the (GROUP BY Category_id) Category_ID ambigous?
you using 2 tables with that as a field.

As for a query that gets all submitters, try

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
GROUP BY publishable_item.Submitter_ID;

Posted: Tue Jul 20, 2004 11:48 am
by davidklonski
I don't want to get all the submitters.

Maybe an example will make it clearer:
- Album 1 was submitted by Submitter 1 and the album belongs to category 1 (title cat1)
- Album 2 was submitted by Submitter 2 and the album belongs to category 2 (title cat2)
- Album 3 was submitted by Submitter 1 and the album belongs to category 2 (title cat2)
- Album 4 was submitted by Submitter 1 and the album belongs to category 2 (title cat2)

There are 2 more categories (3 & 4) named (cat3 & cat4 respectively)

The output of the query should give:

Code: Select all

category_ID  Title  COUNT(photo_album.Album_ID)
1            cat1   1
2            cat2   2
3            cat3   0
4            cat4   0
Note that submitter 2 also submitted an album that belongs to one of the cateogories, but the query should count only those albums that submitted by submitter 1

Currently, the query returns:

Code: Select all

category_ID  Title  COUNT(photo_album.Album_ID)
1            cat1   1
2            cat2   2
Note that categories 3 & 4 are not listed! and this is the error

Posted: Tue Jul 20, 2004 11:58 am
by feyd
doing a bit of testing... switch the inner join to left join.

Posted: Tue Jul 20, 2004 12:42 pm
by liljester
lol im so confused.... do you only want the results from a specific submitter? all submitters? sorted by submitter? because in your demo output, it doesnt say anything about the submitter

Posted: Tue Jul 20, 2004 12:51 pm
by davidklonski
I tried changing it into a left join but I am getting all the albums, not only the ones for submitter 1

It is as if the condition on the submitter has no effect

Posted: Tue Jul 20, 2004 12:53 pm
by feyd
LEFT JOIN seems to work for me, could you post some of the data in each of the tables, so I can play around with it some?

Posted: Tue Jul 20, 2004 12:59 pm
by davidklonski
I will repeat the query just to make things clear:

Code: Select all

SELECT album_categories.*, 
           COUNT(photo_album.Album_ID)  NumAlbums
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 album_categories.Category_ID;
The query should return information about categories.
Info on the categories will include all the information that is defined in the categories table + for each category, the number of albums that belong in that category.

Taking my example into account, if I want to get the information without taking submitters into account, I should get:

Code: Select all

category_ID  Title  NumAlbums 
1            cat1   1 
2            cat2   3
3            cat3   0 
4            cat4   0
Note that for category 2 there is a total of 3 albums (two by submitter 1 and one more by submitter 2)

Now, if I want to get the same info, but only take submitter 1 into account I should get:

Code: Select all

category_ID  Title  NumAlbums
1            cat1   1 
2            cat2   2
3            cat3   0 
4            cat4   0
Now the album count should only take into account albums submitted by submitter 1

I hope this makes things simpler

Posted: Tue Jul 20, 2004 1:06 pm
by davidklonski
feyd, could you post the query that you say seem to work for you and I will try it?

Posted: Tue Jul 20, 2004 1:11 pm
by feyd

Code: Select all

SELECT album_categories .  * , COUNT( photo_album.Album_ID ) NumAlbums
FROM album_categories
LEFT  JOIN photo_album ON album_categories.Category_ID = photo_album.Category_ID
LEFT  JOIN publishable_item ON publishable_item.Item_ID = photo_album.Album_ID AND publishable_item.Submitter_ID =1
GROUP  BY album_categories.Category_ID
I'm pretty sure my data's off, because I get differing numbers, but the result seems to have all the elements you want..

Posted: Tue Jul 20, 2004 1:17 pm
by liljester
*light bulb turns on* i just figured out what you wanted lol

you wherent getting the desired results because of your inner join, it only joins on tables where values exist in both tables, thats why you didnt get all your categories... they didnt all exist in both tables.. a left join will look at the table on the left and if it doesnt exist on the right you will still get a row, however the fields from the right table will be null. =)

good show feyd =)

Posted: Tue Jul 20, 2004 1:21 pm
by davidklonski
feyd, I just tried your query and this is what I get:
I truely get all the information on the categories (as I suspected I would get).
However the number of albums per categories is wrong. I get the total number of albums per category without taking the submitter into account.

I even tried changing the submitter ID from 1 to 2 or 3 and I get the same results...

Posted: Tue Jul 20, 2004 1:23 pm
by feyd
post some data so I can fiddle with it some more..