Selecting data from more than 2 tables using join

I am trying to combine data from 3 tables and I am having trouble getting the exact syntax.

I have a set of data as below, showing the history of who has done what with a record. The unique identifier for each record is shown in 'ID' and 'Rec No' is the sequential number assigned to each interaction with the record.

ID  Rec No Who     Type  
1   1      Bob     New  
1   2      Bob     Open  
1   3      Bob     Assign  
1   4      Sarah   Add  
1   5      Bob     Add  
1   6      Bob     Close  
2   1      John    New  
2   2      John    Open  
2   3      John    Assign  
2   4      Bob     Assign  
2   5      Sarah   Add  
2   6      Sarah   Close   
3   1      Sarah   New  
3   2      Sarah   Open  
3   3      Sarah   Assign  
3   4      Sarah   Close  

I need to find all of the 'Assign' operations. However where multiple 'Assign' are in a certain ID, I want to find the first one. I then also want to find the name of the person who did that.

The code I have at the moment is-

mysql> SELECT IH.WHO, COUNT(IH.ID)  
    -> FROM INCIDENTS_H IH  
    -> JOIN(  
    -> SELECT ID, MIN(RECNO) AS RECNO  
    -> FROM INCIDENTS_H  
    -> WHERE TYPE = 'ASSIGN'  
    -> GROUP BY ID  
    -> )IH2  
    -> ON IH2.ID = IH.ID AND IH2.RECNO = IH.RECNO  
    -> GROUP BY IH.WHO  
    -> ;  

which produces the output

+------+--------------+  
| WHO  | COUNT(IH.ID) |  
+------+--------------+  
| Bob  |            1 |  
| John |            1 |  
+------+--------------+  

So far so good. However I also need to restrict the output based on the date, which is held in another table called Incidents. The data in this table is in the format-

+------开发者_如何学Python+------------+  
| ID   | ADDEDDATE  |  
+------+------------+  
|    1 | 2011-06-01 |  
|    2 | 2011-06-03 |  
+------+------------+  

The ID column is the same number in both tables and therefore can be used to find out which ADDEDDATE to use. So can anyone assist with the code to restrict the output to records with an added date of 2011-06-03, by modifying the code above?

So ideally the output would be-

+------+--------------+  
| WHO  | COUNT(IH.ID) |  
+------+--------------+  
| John |            1 |  
+------+--------------+  

I am using mysql 5.1.40

---

Just join to incident and add a where clause :

SELECT  IH.WHO, 
        COUNT(IH.ID)
FROM INCIDENTS_H IH
    INNER JOIN(
        SELECT ID, MIN(RECNO) AS RECNO
        FROM INCIDENTS_H
        WHERE TYPE = 'ASSIGN'
        GROUP BY ID
    )IH2
        ON IH2.ID = IH.ID AND IH2.RECNO = IH.RECNO
    INNER JOIN INCIDENTS I
        ON IH.ID = I.ID
WHERE I.ADDEDDATE = DATE('2011-06-03')
GROUP BY IH.WHO

---

This is my attempt, based on how I understand the question.
If I'm not understanding it correctly, please leave a comment and I will improve what I've put.

I think you're almost there, and that the following should work for you:

SELECT IH.WHO,
        COUNT(IH.ID)
FROM    INCIDENTS_H IH
JOIN   ( SELECT ID, MIN(RECNO) AS RECNO
         FROM INCIDENTS_H
         WHERE TYPE = 'ASSIGN'
         GROUP BY ID
        ) IH2
ON      IH2.ID = IH.ID
AND     IH2.RECNO = IH.RECNO
JOIN    INCIDENTS I
ON      I.ID = IH.ID
WHERE   I.ADDEDDATE = "2011-06-03"
GROUP BY IH.WHO


Just as a note: the primary key for INCIDENTS_H is (Id, RecNo).
Also, it may be worth noting that in order for the database to be in 4th Normal Form, there needs to be a table whose primary key is "Id". Such a table will make your life easier.