SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem in formating a query

    Hello

    I have two tables:
    CREATE TABLE photo_albums (
    Album_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Category_ID INT UNSIGNED NOT NULL,
    PRIMARY KEY (Album_ID),
    FOREIGN KEY photo_albums_Category_ID_fk (Category_ID) REFERENCES album_categories(Category_ID)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    CREATE TABLE album_categories (
    Category_ID INT UNSIGNED NOT NULL,
    Title VARCHAR(255) NOT NULL,
    Description VARCHAR(255) NOT NULL,
    PRIMARY KEY (Category_ID)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    An album belongs to a specific category using the photo_albums.Category_ID attribute.

    I would like to get the list of all the categories in the database (select * from album_categories). For each category, I would like to know how many albums belong to it.

    Here was my best attempt:
    SELECT album_categories.*, COUNT(distinct photo_albums.Album_ID) FROM album_categories, photo_albums WHERE photo_albums.Category_ID = album_categories.Category_ID GROUP BY Category_ID;

    The problem is if I some category is not used in any of the albums, I don't get it back in the results.

    Here is what I would like to get:
    (Category_ID, Title, Description, num of albums under that categoryID)
    (1, Vacations, These are photos from my vacations, 4)
    (2, Events, Special events, 0)
    ...

    Note the would like the query to return 0 if no album is using that category.

    thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you need to learn about outer joins

    also, you need to know that you cannot use "select star" with GROUP BY
    Code:
    select A.Category_ID 
         , A.Title
         , A.Description
         , count(P.Album_ID) as albums
      from album_categories as A
    left outer
      join photo_albums as P
        on A.Category_ID
         = P.Category_ID
    group
        by A.Category_ID 
         , A.Title
         , A.Description
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •