Geek asks and answers

SQL queries, I need to get some values

I have 2 tables, table1 and table2.

table1 have fields:

id
atype
adesc
aid

table2 have fields:

id
aid
adesc
value_1
value_2



$query1 = mysql_query("Select DISTINCT atype from table1");
while($row = mysql_fetch_array($query1)){
    $atype = $row['atype'];
    $query2 = mysql_query("Select adesc from table1 where atype='$atype' and aid IN (Select aid from table2 ) order by id asc");
    while($row2 = mysql_fetch_array($query2)){
        // i know query2 can only get adesc, so i need value_1 and value_2 in this
        echo $row2['adesc'] .'>> '. (this should be value1 from table2) .'>> '. (this should be value2 from table2);
    }
}


I want to get value_1and value_2too. Any help would be appreciated.




EDIT:
table1 values ​​are in my database (atype, aid, adesc respectively):

type1 111 'this is type 1'
type1 111 'this is type 1'
type2 112 'this is type 2'
type3 113 'this is type 3'
type4 114 'this is type 4'
type1 111 'this is type 1'
type4 114 'this is type 4'
type2 112 'this is type 2'

on my table2 (help, adesc, val1, val2, respectively):

111  'this is type 1' 100 50
111  'this is type 1' 100 50
112  'this is type 2' 300 500
113  'this is type 3' 100 50
112  'this is type 2' 100 50
114  'this is type 4' 100 50
111  'this is type 1' 100 50

what i really want to project:

type1
      (sum)value_1 (sum)value_2
type2 
      (sum)value_1 (sum)value_2
type3
      (sum)value_1 (sum)value_2
type4
      (sum)value_1 (sum)value_2
+3
source share
3 answers
    $query2 = mysql_query("Select t1.adesc, t2.value_1, t2.value_2 from table1 as t1, table2 as t2 where t1.atype='$atype' and t1.aid = t2.aid order by t1.id asc");

    while($row2 = mysql_fetch_array($query2)){
        // i know query2 can only get adesc, so i need value_1 and value_2 in this
        echo $row2['adesc'] .'>> '. $row2['value_1'] .'>> '. $row2['value_2'];
    }

UPD:

$sql = <<<SQL
select t1.adesc, sum(t2.value_1) as v1, sum(t2.value_2)  as v2
from 
table1 as t1,
table2 as t2

where 
t1.aid = t2.aid 
group by t1.atype

order by t1.atype asc
SQL;
$query = mysql_query($sql);
while($row = mysql_fetch_array($query2)){
    // i know query2 can only get adesc, so i need value_1 and value_2 in this
        echo $row['adesc'] .'>> '. $row['v1']  .'>> '. $row['v2'];
}
+1
source

Use one simple join in SQL, not your two choices:

select table1.adesc,atable2.value1 from table1,table2 where table2.adesc=table1.adesc and table1.aid=table2.aid order by table1.aid asc

Edit:

select table1.adesc,atable2.value1 from table1,table2 where table1.aid=table2.aid order by table1.aid asc
0
source

This SQL query should provide the desired results:

select adesc, 
       value_1, 
       value_2 

from   table1 

where  atype='$atype' and 
       aid IN (Select aid from table2 ) 

order by id asc
-1
source

More articles:


All Articles