基于两个表的MYSQL计数

Question

Sorry for asking this, but I haven't found an answer to what I'm trying to do anywhere!

Basically, I have a database with two tables. Below or two examples I'll use:

Table 1:

Process ID  Date
---------- -----------
         1  2008/08/21
         2  2008/08/23
         3  2008/08/21

Table 2:

Process ID Qty
---------- ---
         1   1
         2   4
         3   6

Basically, I was to do something in PHP where I will select table 1, and find all processes that occur today (in this example I'll say the 21st of August). I then want to take those process ids, and match them in Table two and give a count of their quantities.

The end result I'm trying to figure out in this example is how do I get the output to be "7" by using PHP to select the processes that happened today in one table, then add up the corresponding process quantities in another table.

Answer 1

SELECT sum(t2.qty)
FROM table1 t1
JOIN table2 t2 ON t1.pid = t2.pid
WHERE t1.date = '2008/08/21'