Subtracting 2 MYSQL arrays

La_Parka

I have two arrays that derive from a MYSQL database, each table with an identical structure:

Table1
+key1
-field1

Table2
+key2
-field2

Where the keys of these two tables coincide, I'm trying to subtract field2 from field 1. Here is what I have so far:

$sql1 = "SELECT key1, field1 FROM table1";
$query1 = @mysql_query($sql1);
$row1 = @mysql_fetch_array($query1);

$sql2 = "SELECT key2, field2 FROM table2";
$query2 = @mysql_query($sql2);
$row2 = @mysql_fetch_array($query2);

$array1 = $row1;
$array2 = $row2;

foreach($array1 AS $key => $val)
{
$newArray[] = $val - $array2[$key];
}

The difference is supposed to be stored in $newArray.

How can I specify that I am trying to obtain the difference between field1 and field2?

Weedpacket

Select
    t1.key1,
    t1.field1-t2.field2 As fielddifference
From
    table1 t1, table2 t2
Where
    t1.key1=t2.key2

La_Parka

Weedpacket wrote:

Select
    t1.key1,
    t1.field1-t2.field2 As fielddifference
From
    table1 t1, table2 t2
Where
    t1.key1=t2.key2

I wish it were that easy. The table info that I gave was purposely simplified for the sake of the thread. Perhaps I should've made note of that.

In actuality, I have two MYSQL arrays. The queries used to obtain them are identical, except for differing "WHERE" parameters. I could solve this with a subquery, but can't grasp the pre-MySQL 4.1 concept of them.

Here are the two actual queries, as they stand:

SELECT h.hintCategoryID AS hintCategoryID, COUNT(*) AS maxHints
FROM hint h
LEFT JOIN chosenhint ch USING (hintID)
WHERE h.questionID = ' ".$questionID." '
GROUP BY h.hintCategoryID;

SELECT h.hintCategoryID AS hintCategoryID, COUNT(*) AS hintsSelected
FROM hint h
LEFT JOIN chosenhint ch USING (hintID)
WHERE h.questionID = ' ".$questionID." '
AND ch.responseID = ' ".$responseID." '
GROUP BY h.hintCategoryID;