how to effectivley query a normilized SQL db w/ aggregats?

Posted on 2011-05-06
Last Modified: 2012-05-11
For context, i am including a DB schema image.

I have an email type application and want to select all the messages (inbox, sent, archived...) for a user. The problem is that i normilize the header part of the emails into the DB such that the flat data goes into a Message table and the from,to,cc,bcc get stored to another table.

whats the best way to select # of messages in each of my desired aggregations such as inbox, sent, archived, read, flagged as per the PK/ FK relationship betweek the message table and the recipient table?

one thing i am placing much weight on is the efficiency of the sql solution because this will be code that gets executed many many times over and will likely be the most run sql of the entire DB

here is how i can get each of my required aggregations:

SELECT * FROM Message...
Inbox                        WHERE [to]    = [ufid] and isArchived = 0
Unread                        WHERE [to]    = [ufid] and isRead = 0
Flagged                        WHERE [to]    = [ufid] and isFlagged = 1
Drafts                        WHERE [to]    = [ufid] and isDraft = 1
Sent                          WHERE [from] = [ufid]
Archived                   WHERE [to]     = [ufid] and isArchived = 1

my desired result would be :
inbox      | 20
unread   | 10
flagged   |   2
draft       |   5
sent        | 11
archived | 12

Question by:realcoding
    LVL 22

    Expert Comment

    See how the following works for you.

    SELECT Folder
                WHEN Type = 'To'
                    AND isArchived = 0
                    AND Folder = 'Inbox'
                THEN 1
                WHEN Type = 'To'
                    AND isRead = 0
                    AND Folder = 'Unread'
                THEN 1
                WHEN Type = 'To'
                    AND isFlagged = 1
                    AND Folder = 'Flagged'
                THEN 1
                WHEN Type = 'To'
                    AND isDraft = 1
                    AND Folder = 'Drafts'
                THEN 1
                WHEN Type = 'From'
                    AND Folder = 'Sent'
                THEN 1
                WHEN Type = 'To'
                    AND isArchived = 1
                    AND Folder = 'Archived'
                THEN 1
                ELSE 0
            END) Tally
        (SELECT F.Folder
        INNER JOIN MessageSource MS
        ON  MS.ID = M.MessageSourceID
        INNER JOIN MessageRecipient MR
        ON  MR.MessageID = M.ID
        INNER JOIN RecipientType R
        ON  R.ID = MR.MessageRecipientID
        INNER JOIN Folder F
        ON  M.FolderID = F.ID
        ON  U.ID = F.UserID
        WHERE RT.Type IN('To'
        ) T;

    Open in new window

    LVL 8

    Expert Comment


    Author Comment

    @8080_Diver: the folders table is for user defined folders only. system folders such as inbox,, sent are defined in the SQL logic in the question, I can derive them based on the logic posted above. this eliminates the need for having folder records for these system folders.

    Author Comment

    8080_Diver: the sql would definitely need a left outer join to folder table to always include folders even if there are no messages in the folder.
    LVL 22

    Accepted Solution

    SELECT 'Inbox' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isArchived = 0 union
    SELECT 'Unread' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isRead = 0 union
    SELECT 'Flagged' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isFlagged = 1 union
    SELECT 'Drafts' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isDraft =1 union
    SELECT 'Archived' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isArchived = 1 union
    SELECT 'Sent' as folder, COUNT(*) as Tally FROM Message...  WHERE [from]=[ufid] 

    Open in new window

    The above is simply a restatement of your original question except in psuedo-SQL.  The part that you don't know how to do (e.g. WHERE [to]=[ufid] and isArchived = 0) is the part I am trying to address.  If you don't include the system folders in the Folders table, how do you plan to get the data associated with the decision rules?  Not including the system folders in your Folder table means that you are going to have to, in effect, plug some information and that means that you a) are not actually pulling the Tally data from your database and b) are introducing potention maintenance issues.

    If you substitute a LEFT OUTER JOIN into the query I provided, it should get you close to the right results.  

    However, if you will go ahead and include the system folders in your Folders table, you don't need to bother with the the LEFT OUTER JOIN if you start from the Folders table.  By doing so, you will ensure that all Folders are included in the output, whether they have messages or not.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now