SQL query: a stored procedure that receive user ProfileId and get table messages contact list

I need a help with a complicated select query.I have message table

CREATE TABLE Message(
    MessageId int IDENTITY(1,1) NOT NULL, /*primary key*/
    FromProfileId int NOT NULL, /*foreign key to Profile table*/
    To开发者_StackOverflow中文版ProfileId int NOT NULL, /*foreign key to Profile table*/
    Datetime datetime NOT NULL,
    MessageText ntext NOT NULL,
    IsNew bit NOT NULL )

I need a stored procedure that receive user ProfileId and get table messages contact list, something like this:

• ContactProfileId (ProfileId that user has sent message or has received message),
• AllMessagesCount (all sent/received messages count to/from ContactProfileId),
• NewMessagesCount (new messages count received from ContactProfileId),
• LastMessageDateTime (last message datetime from/to ContactProfileId),
• some ContactProfileId profile information joined from Profile table.

Any ideas how can I achieve that result?

Updated:Here Is the example of some data

MessageId FromProfileId ToProfileId messageDatetime          IsNew
--------------------------------------------------------------------
1         2              3          2010-11-20 18:16:40.230  1
2         2              3          2010-12-20 18:16:40.230  1
3         3              2          2010-10-20 18:16:40.230  0
4         3              4          2010-12-25 18:16:40.230  1

result of SP when parameter ProfileId=3 must be this

ContactProfileId  AllMessagesCount  NewMessagesCount  LastMessageDateTime      ContactName
---------------------------------------------------------------------------------------------------------------------
2                  3                2                2010-12-20 18:16:40.230   Contact Name joined from Profile Table 
4                  1                0                2010-12-25 18:16:40.230   Contact Name joined from Profile Table

You're not quite clear on what you want to get, and how - do you want to get back your values as output parameter from the stored proc, or as an output result set??

Also, the first and last point in your list of requirements is very vague.... can you elaborate?? It almost seems as if you want lists of profile ID's - right?

Here's a first attempt:

 CREATE PROCEDURE dbo.DoSomething @ProfileID INT
 AS BEGIN
    DECLARE @AllMsgCount INT, @NewMsgCount INT
    DECLARE @LastMsgDate DATETIME 

    -- count all messages
    SELECT @AllMsgCount = COUNT(*)
    FROM dbo.[Message] 
    WHERE (FromProfileId = @ProfileID OR ToProfileId = @ProfileID)

    -- count new messages
    SELECT @NewMsgCount = COUNT(*)
    FROM dbo.[Message] 
    WHERE FromProfileId = @ProfileID
      AND IsNew = 1

    -- determine last datetime
    SELECT @LastMsgDate = MAX([Datetime])
    FROM dbo.[Message] 
    WHERE (FromProfileId = @ProfileID OR ToProfileId = @ProfileID)

    -- return three values as a result set
    SELECT @AllMsgCount, @NewMsgCount, @LastDateTime

    -- ProfileId that user has sent message to or has received message from
    -- select all profile ID's where the user has sent a message to, or 
    -- received a message from; return as a second result set
    SELECT DISTINCT ToProfileId
    FROM dbo.[Message] 
    WHERE FromProfileId = @ProfileID
    UNION ALL
    SELECT DISTINCT FromProfileId
    FROM dbo.[Message] 
    WHERE ToProfileId = @ProfileID

    -- return from profile data from the profile table
    SELECT (list of columns)
    FROM dbo.Profile
    WHERE ProfileId = @ProfileID

END

Also: you might want to find a more clever name for your DATETIME column than Datetime..... same goes for Message which is also a SQL Server reserved word and shouldn't be used as such for a column name...

Furthermore: based on the Single Responsibility Principle, I think it's a bad architecture to have a single stored procedure that returns all sorts of different data from various places. I would recommend having stored proc that return one set of data each - if you need three sets of data, have three procedures..... makes maintenance just that much easier!

Update: my second attempt, leveraging some of the input that Richard provided: I'm using a CTE (Common Table Expression) to get the data you might be interested in. Still: i believe your sample data provided has errors - please check!

 CREATE PROCEDURE dbo.DoSomething @ProfileID INT
 AS 
    -- this CTE finds all distinct ProfileID's that the one passed in
    -- as a parameter has had message exchange with

    ;WITH Contacts AS
    (
        SELECT DISTINCT ToProfileID AS 'ProfileID'
        FROM dbo.Messages
        WHERE FromProfileId = @ProfileID

        UNION 

        SELECT DISTINCT FromProfileID AS 'ProfileID'
        FROM dbo.Messages
        WHERE ToProfileId = @ProfileID
    )
    SELECT
       c.ProfileID,
       COUNT(*) AS AllMessagesCount,
       COUNT(CASE WHEN IsNEW = 1 AND FromProfileID IS NOT NULL THEN 1 ELSE NULL END) AS NewMessagesCount,
       MAX(Datetime) AS LastMessageDateTime,
       p.ContactName
    FROM
       Contacts c  -- our CTE with the communication partners
    INNER JOIN 
       Messages m ON (m.FromProfileId = c.ProfileID OR m.ToProfileId = c.ProfileID)
    INNER JOIN 
       dbo.profiles p ON c.ProfileID = p.ProfileID
    GROUP BY
       c.ProfileID,  p.ContactName

This would produce an output something like this (I took the liberty of adding some sample ContactName to the Profiles table):

ProfileID  AllMessagesCount  NewMessagesCount  LastMessageDateTime      ContactName
   2              3                2           2010-11-20 18:16:40.230    David
   4              1                1           2010-12-25 18:16:40.230    Thomas

SELECT m.*, p.Name AS ContactName
FROM (
  SELECT
    ContactProfileId,
    AllMessagesCount = COUNT(*),
    NewMessagesCount = COUNT(CASE IsNew WHEN 1 THEN 1 END),
    LastMessageDateTime = MAX(messageDateTime)
  FROM (
    SELECT
      ContactProfileId =
        CASE ToProfileId
          WHEN @ProfileId THEN FromProfileId
          ELSE ToProfileId
        END)
      messageDateTime,
      IsNew
    FROM Message
    WHERE @ProfileId IN (FromProfileId, ToProfileId)
  ) m
  GROUP BY ContactProfileId
) m
  INNER JOIN Profile p ON m.ContactProfileId = p.Id

Or, using CTEs:

WITH filteredmessages AS (
  SELECT
    ContactProfileId =
      CASE ToProfileId
        WHEN @ProfileId THEN FromProfileId
        ELSE ToProfileId
      END)
    messageDateTime,
    IsNew
  FROM Message
  WHERE @ProfileId IN (FromProfileId, ToProfileId)
),

groupedmessages AS (
  SELECT
    ContactProfileId,
    AllMessagesCount = COUNT(*),
    NewMessagesCount = COUNT(CASE IsNew WHEN 1 THEN 1 END),
    LastMessageDateTime = MAX(messageDateTime)
  FROM filteredmessages
  GROUP BY ContactProfileId
)

SELECT m.*, p.Name AS ContactName
FROM groupedmessages m
  INNER JOIN Profile p ON m.ContactProfileId = p.Id

DECLARE @ProfileID int

SET @ProfileID = 1 --here you will need to set it to the profile id you want to query for

SELECT
    count(*) AS AllMessagesCount,
    count(CASE WHEN IsNEW = 1 AND FromProfileID IS NOT NULL THEN 1 ELSE NULL END) AS NewMessagesCount,
    MAX(DateTime) AS LastMessageDateTime,
    /*some other profile data from the profile table joined in*/
FROM
    Message m JOIN
    ProfileTable pt ON m.FromProfileID = pt.ProfileID
WHERE m.FromProfileID = @ProfileID OR m.ToProfileID = @ProfileID