Query Help Part II

ledtear

Given the following tables:

Table 1
col1 | col2
1 | AAA
2 | BBB
3 | CCC
4 | DDD
5 | EEE

Table 2
col1 | col2
2 | 65
3 | 78
4 | 34
4 | 10
2 | 34
1 | 12

Whereas Table1.col1 and Table2.col2 match - I need to show DISTINCT Table1.col1 with its AVERAGE Table2.col2 order by that average.... make sense?

bpat1434

Assuming you're using MySQL....

You want to query Table 1 and get the corresponding values from Table 2 (where table1.col1 = table2.col1)

SELECT table1.*
FROM table1
LEFT JOIN table2
    ON table1.`col1` = table2.`col1`

where table1.col1 is only shown once in the result set

SELECT table1.*
FROM table1
LEFT JOIN table2
    ON table1.`col1` = table2.`col1`
GROUP BY table1.`col1`

all while finding the average of the values in table2.col2?

SELECT table1.`col1`, AVG(table2.`col2`)
FROM table1
LEFT JOIN table2
    ON table1.`col1` = table2.`col1`
GROUP BY table1.`col1`

The last query should return the table1.col1 and the average of it's corresponding values from table2. The GROUP BY ensures that there are no duplicates in the result set.