SQL: another easy question

selecting count and joining, again

[httpwebwitch]

I have two tables, categories and product_cat.
The catgories are just the names and numbers of categories:

CATEGORIES
ID name
1 light stuff
2 heavy stuff
3 expensive stuff
4 cheap stuff

product_cat contains products assigned into those categories:

PRODUCT_CAT
ID category product
1 1 3
2 1 4
3 2 15
4 3 2

I need to get the names of the categories, and how many items are in each. The way I'm doing it now is stupid, and takes a long time to process.

$query="SELECT * FROM categories";
$result=mysql_query($query);

while($row=mysql_fetch_array($result)){
// loop thru categories and do a
// separate query for the count()

$query="SELECT COUNT(*) as count FROM product_cat WHERE category=".$row['categoryID']."";
$rresult=mysql_query($query);
$rrow=mysql_fetch_array($rresult);

print($row['name']." (".$rrow['count'].")<BR>");

}

I'm pretty sure it can be done with a joined table, but I've been hacking at the query for a while and nothing has worked yet.

Help with this specific situation is appreciated...

Can anyone recommend a good book for someone who is otherwise quite intelligent, but gets flummoxed by complex SQL queries?

[timster]

Your on the right track with the JOIN. You also need an "aggregate with grouped results."

I think this will do it:

SELECT DISTINCT pc.category, cat.name, COUNT(*) AS total 
FROM product_cat
JOIN CATEGORIES AS cat ON cat.ID = pc.category
GROUP BY pc.category

Don't be surprised that you feel your "starting on the ground floor" with SQL -- it's fundementally different than the other kinds of scripting Webfolk do.

(Sorry I can't recommend a good book. I hated the ones I used.)

[httpwebwitch]

sorry, no it didn't work

[httpwebwitch]

the categories are listed, but the totals are all wrong

[Netizen]

It is possible the original answer from timster was wrong - I haven't had experience with GROUP BY and DISTINCT at the same time.

I would do

SELECT  
   pc.category,  
   cat.name,  
   COUNT(*) AS total 
FROM  
   product_cat AS pc  
INNER JOIN  
   categories AS cat  
ON  
   (cat.ID = pc.category) 
GROUP BY  
   pc.category 

which should work - I do it all the time. Good luck.

[henry0]

Good Book
Too heavy for a trip abroad but great book!
<<<
MySQl
"The definitive guide to using programming and administrating MySQl DB"
>>>

Second edition (verify it reads: Second edition)
by Paul Dubois
@ Developper's Library

have fun :)

regards

Henry

[timster]

Oops, sorry should've tested my code. Netizen's query works, but it doesn't return a line for "cheap stuff" because there are no lines for it in the product_cat table.

Tweak it like this to get the "total = 0" results:

SELECT 
  cat.id, 
  cat.name, 
  COUNT(pc.product) AS total 
FROM 
  product_cat AS pc 
RIGHT JOIN 
  categories AS cat 
ON 
  (cat.ID = pc.category) 
GROUP BY 
  pc.category

BTW, the "DISTINCT" didn't break it (at least not in MySQL 3.x here) but it's unnecessary.

[httpwebwitch]

that worked! thanks! this is the final version:

SELECT
cat.categoryID,
cat.categoryName,
COUNT(pc.product) AS total
FROM product_cat
AS pc
RIGHT JOIN categories AS cat
ON (cat.categoryID = pc.category)
GROUP BY pc.category

The query below also worked, adding a WHERE clause to show only the first-level child categories from the root of a hierarchical tree.

SELECT
cat.categoryID,
cat.categoryName,
cat.categoryParent,
COUNT(pc.product) AS total
FROM product_cat
AS pc
RIGHT JOIN categories AS cat
ON (cat.categoryID = pc.category)
WHERE cat.categoryParent=0
GROUP BY pc.category