TSQL Cross Apply not working as expected

Question

Have 3 tables:

• Users
• Depts (although seems not necessary for the query but...)
• Messages

Users:

       UID, DeptID, Name, Surname,  Admin
        U1  DeptA   John    Doe     0
        U2  DeptB   Jack    Fisher  0
        U3  DeptA   Jill    Smith   0
        U4  DeptA   Joan    Green   1
        U5  DeptC   Jeff    Jones   1
        U6  DeptA   Amanda  Reed    0
        ......

Depts:

       DeptID, DeptName
        DeptA   Denver
        DeptB   Boston
        DeptC   Seattle

Messages:

        Sender, Receiver, Message, CreatedDate, Seen
            U1      U3      Msg1        20152310    0
            U1      U4      Msg2        20152310    0
            U4      U1      Msg3        20152310    0
            U3      U4      Msg4        20152310    0
            U1      U3      Msg5        20152310    0
            U3      U1      Msg6        20152310    0

(Users can send messages only to other users in same Dept)

with as input only one parameter, thus UID

I need to create a list with all Users of same Dept (except input UID) as headers, therefore in the example

U3          U4       U6

with their details (name, surname) to create the tabs list

and then for each of the UID above, a list with latest 20 messages where U1 is involved both as sender and receiver

To fill the chat sidebar you can see here: http://www.keenthemes.com/preview/metronic/theme/admin_4/

clicking on the top right icon

I tried with this.. but have plenty of duplicates that do not know how to remove: perhaps cross apply is not the right command... :-(

SELECT  u.UID, 
        u.PWD,
        i.Sender,
        I.MessageText
        .....
FROM (
        SELECT  S.UID,
                S.PWD
            FROM dbo.Users  u
            JOIN dbo.Users  u2  ON  u.DeptID=u2.DeptID
            WHERE u2.UID=@_UID
            ORDER BY u.Admin DESC
        ) AS u
CROSS APPLY 
        (
            SELECT TOP (20) 
                    m.Sender, 
                    m.Message, 
                    m.CreatedDate, 
                    m.Seen
            FROM    dbo.Messages m
            WHERE   m.Sender=@_UID 
            OR      m.Receiver=@_UID
            ORDER BY m.CreatedDate DESC
        ) m;

Can suggest what's wrong?

Thanks

Joe

Answer 1

Honestly, I can't figure out exactly what your query was trying to do. But from looking at the page you linked and the description of your problem, it sounds like you want a query to populate the list of users (other than current user) and another to get the messages that pertain to that user.

Consider something like:

DECLARE @_UID varchar(2) = 'U3';

-- Query for populating list of users
SELECT [UID], [Name] + ' ' + [Surname] AS [FullName]
FROM [dbo].[Users]
WHERE [UID] <> @_UID
ORDER BY [Admin] DESC, [Surname], [Name];

-- Query for populating messages
SELECT TOP 20 S.[UID] AS [SenderUID], S.[Name] + ' ' + S.[Surname] AS [SenderFullName],
    R.[UID] AS [ReceiverUID], R.[Name] + ' ' + R.[Surname] AS [ReceiverFullName],
    M.[Message], M.[CreatedDate], M.[Seen]
FROM [dbo].[Messages] M
    INNER JOIN [dbo].[Users] S ON S.[UID] = M.[Sender]
    INNER JOIN [dbo].[Users] R ON R.[UID] = M.[Receiver]
WHERE @_UID IN(M.[Sender], M.[Receiver])
ORDER BY M.[CreatedDate] DESC;

Regarding the usage of APPLY, it is similar to JOIN. When using JOIN, you are combining a set of results that is known (such as the contents of a table) to another set of results that is also known. When using APPLY, the right side set of results do not necessarily have to be known - for example, you can use a function to calculate a result based on the left side results.