查询显示php mysql两个不同列的值

Question

i have two tables with fields and values:

TABLE respostas

Id      Corp_resp            Id_perg     Id_quest 
12       0,2                 1             1
13       0,5                 2             1
14       0,7                 3             1
15       0,2                 4             1
16       0,3                 5             1
17       0,6                 6             1

Table menu

Id_perg         percentagemadministrador Id_quest
1                             0.5        1    
2                             0.4        1         
3                             0.3        1          
4                             0,2        1
5                             0,1        1
6                             0,8        1

I want to associate the id_quest to id_perg and multiply the corp_resp with percentagemadministrador of the last 10 rows of the table respostas

total=SUM(corp_resp*percentagemadministrador) of the last 10 rows of the table.

<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("teste") or die(mysql_error()); // selecciona a database do server escolhido

//something like that

$query ="SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
         FROM respostas 
         INNER JOIN menu 
         WHERE respostas.id_perg=menu.id_perg 
         AND respostas.id_quest=menu.id_perg 
         LIMIT id_perg=10";

$resultado = mysql_query($query) or die(mysql_error());

if($row = mysql_fetch_array($resultado)){

    echo " <center> Final: </center> ".$row['TOTAL'];
} ?>

Can you help with that?

Answer 1

I think you should use a subselect for getting the 10 firts id only

SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
FROM respostas INNER JOIN menu 
WHERE respostas.id_perg=menu.id_perg 
AND respostas.id_quest=menu.id_perg 
AND id_perg in (select id_perg from respostas order by id_perg limit 10);

if don't work in we can try with inner join

SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
FROM respostas 
INNER JOIN menu on ( respostas.id_perg=menu.id_perg 
     AND respostas.id_quest=menu.id_perg)  
INNER JOIN  ( select id_perg from  respostas order by id_perg limit 10) as t2 
 on (respostas.id = t2. id)