Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45

    Question Unanswered: Nested SELECT query that also returns COUNT from related table

    OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

    Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

    CatID | Name | Description | No. Products

    0001 | Cars | Blah blah blah | 5

    etc etc

    At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

    However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

    Many thanks!
    Sunday Ironfoot

    SQL Server 2000 n00b

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use an aggregate query:

    Code:
    select	Category.CatID,
    	Category.Name,
    	...
    	count(distinct Product.ProductID) ProductCount
    from	Categories
    	left outer join Products on Categories.CategoryID = Products.CategoryID
    group by Category.CatID,
    	Category.Name,
    	...
    order by count(distinct Product.ProductID)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45
    Absolutely brilliant, it works fantastically, thank you so much!!

    Now to try and figure out how it actually works
    Sunday Ironfoot

    SQL Server 2000 n00b

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you use Books Online to figure out how this query works, you can consider yourself to have passed SQL 101. It incorporates the most fundamental aspects of SQL programming.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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