[RESOLVED] select statement 2 tables

SELECT avg(tblData.MV), tblStandard.MV_MIN FROM tblData, tblStandard WHERE tblData.COMPOUND_NO='123'

i've got this error:
"you try to execute a query does not include the specified expression 'MV_MIN
as part of an aggregate function.'"

above code would select/compute the average of tblData.MV
and list tblStandard.MV_MIN, when i try to remove avg() and remain the tblData.MV no error found.

any idea?


tnx

sorry i already solved this one.
i found on the net using subquery

SELECT avg(tblData.MV) as ave, (SELECT tblStandard.MV_MIN FROM tblStandard WHERE COMPOUND_NO='123' ) as min FROM tblData WHERE COMPOUND_NO='123'

SELECT avg(tblData.MV), tblStandard.MV_MIN FROM tblData INNER JOIN tblStandard ON tblData.COMPOUND_NO = tblStandard.COMPOUND_NO WHERE tblData.COMPOUND_NO='123' GROUP BY tblStandard.MV_MIN

- You should use a join to link your tables. Now it Links all rows from the two tables with eachother. Linking tables should be quicker that executing a sub-query per record.

You should use group by. In this exapmle you group by the field tblStandard.MV_MIN. This means it calculates the average of tblData.MV per value of tblStandard.MV_MIN.