[SOLVED] Counting with joins

[benjam]

I would like to pull data from three tables (one is basically a linking table) where the query counts the number of instances of a certain value as well as the total number of rows.

Something like this:
[code]SELECT A.name
  , A.id
  , COUNT(C.value != 0) AS filledValues      -- i know this isn't legal, but you get the idea
  , COUNT(C.*) AS totalValues
FROM TableC AS C
  LEFT JOIN TableB AS B
    ON B.tableC_id = C.id
  LEFT JOIN TableA AS A
    ON A.id = B.tableA_id
GROUP BY A.id
ORDER BY A.name[/code]

I know that query will not work, but I was hard pressed to find an easier way to explain what I wanted as output.

I want a list of all entries in table A with counts of various bits in table C, whether they are zero or not.
I also want the count of entries in C that have no related entries in B or A (hence pulling from that table first).

Does anybody have any suggestions?  Or is there some simple thing that I am just missing?

Or do I just need to loop through it in PHP and count my values that way?

Thanks in Advance

[benjam]

And I'm going for speed here, this is a stats query that will be run by an over zealous marketing guy in our office at least once every 15 minutes on a database with hundreds of thousands of entries (in TableC, TableA might only have 20 or so).  So if running this in PHP will be faster, then I'll do it that way, I've just always heard that MySQL is faster than PHP when it comes to counting things.

Also, if you have any suggestions on indexes that would speed up this query (if it's determined that that method is faster), please let me know.

And there is an error in the query, it should be:
[code]SELECT A.name
  , A.id
  , COUNT(C.value != 0) AS filledValues      -- i know this isn't legal, but you get the idea
  , COUNT(C.*) AS totalValues
FROM TableC AS C
  LEFT JOIN TableB AS B
    ON B.id = C.tableB_id    -- this bit was changed from above (C relates to B, not vice versa)
  LEFT JOIN TableA AS A
    ON A.id = B.tableA_id
GROUP BY A.id
ORDER BY A.name[/code]

[fenway]

Well, you could always use a SUM() with a nested IF() to determine the value.

[benjam]

But I don't want the sum, unless I am misunderstanding you...
Could you give me more details please?

I want the number of rows that have a value not equal to zero that are related to the TableA entry as well as the total number of rows that are related to the TableA entry.

Is this what you were thinking?

[fenway]

Sure... and if whatever value you're checking is valid, add one, otherwise, add zero -- effectively the same as count.

[benjam]

Thanks, works like a charm.

So for those of you following this, the query is:
[code]SELECT A.name
  , A.id
  , SUM(IF(C.value != 0, 1, 0)) AS filledValues
  , COUNT(C.id) AS totalValues
FROM TableC AS C
  LEFT JOIN TableB AS B
    ON B.id = C.tableB_id
  LEFT JOIN TableA AS A
    ON A.id = B.tableA_id
GROUP BY A.id
ORDER BY A.name[/code]

One thing that didn't make sense, when I ran the query with the totalValues being counted as COUNT(C.*) it returns an error at that location.  But COUNT(C.id) is fine, and works as expected.

What might be the reason for this?

[fenway]

"*" is magic for COUNT()... it's not the same as "*" in the SELECT column list.  Either you count everything (*), or you can count non-NULL values for a given field -- which is what you did.