Joining two tables using a count
- by Dave
I have two tables as defined below:
EMAILS
ID SUBJECT BODY
1 Test1 blah blah
2 Test2 blah blah blah
3 Test3 more blah
ATTACHMENTS
ID EMAIL_ID ATTACHMENT NAME
1 2 TEST.DOC
2 2 TEST.XLS
I'm trying to perform a select to get back all of the emails and a count of how many attachments (if any) for each row in the EMAILS table -- preferably in a single query where the results would resemble something like:
EMAIL_ID SUBJECT NUM_ATTACHMENTS BODY
1 Test1 0 blah blah
2 Test2 2 blah blah blah
3 Test3 0 more blah
I've tried everything but the "right way" and if anyone has a few minutes to help me create the right SQL to build this, I'd greatly appreciate it. Thanks.