Count-problem

[robelind]

Hi.

I'm having problems achieving the following:
Three tables are involved:
A: a_id, a_value
B: b_id, a_id, b_value
C: c_id, b_id, c_value
Between A and B, there'a one-to-one relationship, and between B and C, there is a one-to-many relationship.
What I want to achieve are rows that look like:
a_value count(C)
for each a_value in table A. 'count(C)' is the number of rows in table C that exist for each a_value.

Hope someone can understand the somewhat abstract explanation of the problem....

[azimmer]

Hi.

I'm having problems achieving the following:
Three tables are involved:
A: a_id, a_value
B: b_id, a_id, b_value
C: c_id, b_id, c_value
Between A and B, there'a one-to-one relationship, and between B and C, there is a one-to-many relationship.
What I want to achieve are rows that look like:
a_value count(C)
for each a_value in table A. 'count(C)' is the number of rows in table C that exist for each a_value.

Hope someone can understand the somewhat abstract explanation of the problem....

SELECT a.a_value, COUNT(c.*)
FROM a INNER JOIN b ON a.a_id=b.b_id
      LEFT OUTER JOIN c ON b.c_id=c.c_id
GROUP BY a.a_value