table join with count() do not work?Thanks

[markshen2004]

I have a question about joining table and using count() at same time.the sql below do not work .

I want to get a series list and list how many product in each series.but it do not work.do you think if using stored procedure can fix it?

Please let me now how to fix it.Thank you for your help.

1 sql statement

SELECT Series.*,count(Products.product_id)
FROM Series,Products
WHERE Series.series_id=Products.series_id
GROUP BY Products.series_id
ORDER BY Products.sort_order

2 the table structure like this

Series

serial_id name is_deleted
1 s1 0
2 s2 0
3 s3 0

Products

product_id name serial_id sort_order is_deleted
1 p1 2 1 0
2 p2 3 2 0
3 p3 3 3 0
4 p4 2 4 0
5 p5 2 5 0

 

[PHV]

Something like this ?
SELECT S.serial_id, S.name, Count(*)
FROM Series S INNER JOIN Products P ON S.serial_id=P.serial_id
GROUP BY S.serial_id, S.name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[markshen2004]

I do not understand that the Count(*) function counts for Series table or counts for Product table.

I like it count for product table.is it right?

Thanks

 

[PHV]

Have you tried and checked ?
The query is an inner join for a 1:n relation grouped by the ForeignKey, so the count(*) return the number of Products for each different Serie.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carp]

Does this do what you want:

SELECT s.name, count(P.product_id)
    FROM Series s,Products p
    WHERE S.series_id=P.series_id
    GROUP BY s.name;

This will give you the series name and how many products are associated with it. If no products are associated, the series name will not show up.

 

[markshen2004]

do you think the code you list here also can add order by statement like

SELECT s.name, count(P.product_id)
FROM Series s,Products p
WHERE S.series_id=P.series_id
GROUP BY s.name;
ORDER BY s.series_id

Thanks

 

[PHV]

Just like this ?
SELECT S.serial_id, S.name, Count(*)
FROM Series S INNER JOIN Products P ON S.serial_id=P.serial_id
GROUP BY S.serial_id, S.name
ORDER BY S.serial_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carp]

SELECT s.series_id, s.name, count(P.product_id)
FROM Series s,Products p
WHERE S.series_id=P.series_id
GROUP BY s.series_id, s.name
ORDER BY s.series_id;