CODEWITHSUNDEEP
mysql
Mark Kasson
Mark Kasson

Reputation: 1710

Subquery or INNER JOIN?

I have 3 tables that represent something like attachments to emails. Accordingly, emails have zero, one or more attachments.

The three tables are Emails, Attachments and Files.

Emails - Each row refers to one email and contains a MessageID

Attachments - Each row contains a MessageID and a FileID. There can be multiple rows with the same Message ID.

Files - Each row refers to one File and contains a FileID and the file size (Size)

I am trying to get a result in mysql that contains a list of EmailID and the total size of all files and having no success. I can get the total of the size of the files using a SUM(Size) and an INNER JOIN, but I can't get a list of EmailID's and total file Sizes.

Should this use multiple INNER JOINs? INNER JOIN and a subquery? And then a GROUP BY? Subquery and then an IN. Even a direction here would be really helpful.

Iterating in PHP seems ridiculously expensive.

Many thanks!

Upvotes: 0

Views: 46

Answers (1)

hjpotter92
hjpotter92

Reputation: 80629

SELECT
    e.msgID,
    SUM(f.size)
FROM emails e
INNER JOIN attachments a
    ON a.msgID = e.msgID
INNER JOIN files f
    ON f.fID = a.fID
GROUP BY e.msgID

I think that should do the trick.

Upvotes: 1

Related Questions