Joining two tables using a count

Question

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.

Answer 1

Have you tried something like this? (untested)

SELECT
    e.id,
    e.subject,
    count(*),
    e.body
FROM
    (SELECT *
    FROM emails e
    JOIN attachments a
    ON e.id = a.email_id)
GROUP BY e.id, e.subject, e.body

Answer 2

I like brydgesk's solution, but would prefer not to use a subquery when possible.
This query will display 0 when there are no attachments for a given email.

SELECT
    a.email_id,
    e.subject,
    e.body,
    COALESCE(count(a.id),0) AS NUM_ATTACHMENTS
FROM
    emails e
    LEFT OUTER JOIN attachments a
    ON e.id = a.email_id
GROUP BY 
    a.email_id, e.subject, e.body

Answer 3

You can do this as a simple subquery in the SELECT clause:

Select E.ID As EMAIL_ID
    , E.SUBJECT
    , ( Select Count(*) 
        From ATTACHMENTS As A
        Where A.EMAIL_ID = E.ID ) As NUM_ATTACHMENTS
    , E.BODY
From EMAILS As E