Messing around with mySQL

I have been occupying myself with some querys today. I’ve been having trouble with my INNER JOINs.

I want to check the average of one column, at the same time as counting entries of that column AND counting entries in another table with the same identifying number… And, and getting a name from a third column!

I finally managed to do all this in one query. I could of course have done it in two, but when I get into my head that something is doable… well then I just have to figure out how to do it.

Here is what I did in the end:

SELECT tbl3.name, AVG(tbl1.value), COUNT(distinct tbl1.date), COUNT(distinct tbl2.date)
FROM tbl1
INNER JOIN tbl3 ON tbl3.id=tbl1.v_id
INNER JOIN tbl2 ON tbl3.id=tbl2.d_id
WHERE tbl1.type='string' AND tbl2.type='string' AND tbl1.discarded=0
GROUP BY tbl2.d_id, tbl3.id
ORDER BY AVG(tbl1.value) DESC, COUNT(distinct tbl1.date) DESC, COUNT(distinct tbl2.date) DESC
LIMIT 0,5;

The distinct inside the COUNT() solved my problem of counting things too many times. *phew* I deserve some food now! =)

EDIT: And this is what I used it for:

More info on here... =)