subquery needed??

TABLE NAME: patient
ssn | Name | Date_Admitted
421-00-1111 | Mark | 2004-01-02
555-11-7777 | Toby | 2004-03-04


TABLE NAME: events
ssn | Event_type | Date_Visit
421-00-1111 | Monthly | 2003-10-10
421-00-1111 | Monthly | 2003-11-11
421-00-1111 | Monthly | 2003-12-12
421-00-1111 | Regular | 2004-01-01
421-00-1111 | Regular | 2004-01-10
555-11-7777 | Regular | 2004-01-10


I'm interested in getting this result (similar to patients table except including two count columns):
SSN | Name | Date_Admitted | Count_Regular | Count_Monthly
421-00-1111 | Mark | 2004-01-02 | 2 | 3
555-11-7777 | Toby | 2004-03-04 | 1 | 0

I have the information above attached to this message in html. It may be easier to view.

The result here is essentially the patients table with two extra columns. These extra columns have the results of a COUNT from the events table. One column is a count of the "monthly" visits of the corresponding ssn value and the other column is a count of the "regular" visits of the corresponding ssn value. The dates are relatively unimportant but if you want to help above and beyond the call of duty, show me how to limit the COUNT values by date (count only those events after a certain date).

This has been boggling me for quite a while now. Please help me learn - my email is here. Thanks!!

What type of DB are you running this against?

S_

mysql 4.0

I don't know MySQL, but here it is in SQL of MS Access. It should be similar, with possible a few syntax changes


SELECT Patient.SSN, Patient.Name, First(Patient.Data_Admitted) AS Data_Admitted, Sum(IIf([Events]![Event_Type]="Monthly",1,0)) AS Monthly, Sum(IIf([Events]![Event_Type]="Regular",1,0)) AS Regular
FROM Events INNER JOIN Patient ON Events.ssn = Patient.SSN
GROUP BY Patient.SSN, Patient.Name;


Results
SSN | Name | Data_Admitted | Monthly | Regular
421-00-1111 | Mark | 1/2/2004 | 3 | 2
555-11-7777 | Toby | 3/4/2004 | 0 | 1

S-