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!Use an aggregate query:

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)|||Absolutely brilliant, it works fantastically, thank you so much!! :D

Now to try and figure out how it actually works :)|||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.

Need urgent help

Hi

I have a strange situation. We have a middle teir application (j2ee appserver) that uses connection pooling.

The client was working fine when all of a sudden it hangs.

I have checked the sp_lock and sp_who2 and there has been no locking/blocking whatsover. This problem stays until we restart the app server. Is there any way that I can further dig into this problem and find it out if it is the db that is causing the issue.

I used sql profiler and I could see some log entries made into the database while client is hanging after that. Also sp_who2 lists the spid for the middle tier connection. It does not however show any locks associated with it. The last batch date however is updated every time the client is opened (when it hangs).

Any pointers as to why this is happening,how to diagnose and prevent this is extermely helpful

From what you're saying, it looks like the problem does not reside in the database. If sp_who2 does not return any values in the BlkBy column then probably it is not a db problem.

Besides locking, another thing that can happen is to have an infinite loop in your T-SQL code. You can verify this by running SQL Profiler.

Just to make sure that the problem does not reside in your database you can kill your db connection (by using Kill SPID in Query Analyzer) and see if the client app still does not respond. If it still doesn't then the problem is in your client app and not in the database.

Hope it helps,

Doru

|||

You might be right......I had also killed the spid on the backend and the client still hangs.....Probably it has got to to do with the txactional setting placed on the middle tier connection,,,,Besides there are no infinite loops on the back end,,,,

Thanks for the post....