Complex join for Rating system

[cohlson2002]

OK, I'm still somewhat new to this, but I've been able to figure out everything else.

I'm trying to build a site rating system. So I need to join 3 tables. Tables 1 and 2 are easy but joining table 3 to them is the problem.

Table1 is the master, table2 joins to it with a 1 to 1 ratio on the ID field (all of table1, blanks on table2 where no match) but table3 I need to do some calculations on. Table3 (the review table) has multiple entries for one ID. I also have 5 seperate rating values that calculate to an overall rating. See below...

Table1 Table2 Table3
idField idField idField Value1 Value2 Value3(calc)
ID=1 ID=1 ID=1 5 6 (value1+value2)/2
ID=2 ID=2 ID=1 6 7 (value1+value2)/2

So I need to have ID=1 (table1&2) and an average of Value1, Value2 and Value3

[rachel2004]

I couldn't make out what the type of calculations you were trying to do, but as far as the JOINs go, you could try this:

SELECT t1.col1, t1.col2, t1.col3, t2.col1, t3.col1
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON (t1.id = t2.id)
LEFT JOIN Table3 AS t3
ON (t1.id = t3.id)
WHERE t1.id = 1;

Once you get that query working, then work the separate issue, which is doing the calculations that you need on table 3.

[cohlson2002]

Table3 has multiple entries for ID, something like:

Table1 ID
Dog
Cat
Fish

Table3 ID|Rating
Dog | 1
Dog | 2
Dog | 3
Cat | 2
Cat | 4
Fish | 2

What I'd like to end up with is an average rating for each ID in table1

Dog | 2
Cat | 3
Fish | 2

Can I join table3 in this manner or do I have to condense table3 first?

[liquorvicar]

SELECT COUNT(*), SUM(rating),name_field FROM table3,table1 WHERE table3.ID=table1.ID GROUP BY table3.ID

you can then just loop through your results and display the name_field and average (SUM(Rating)/COUNT(*) assuming you wanted the mean average).