CODEWITHSUNDEEP
c#sql-serversql-server-2008
Apollo
Apollo

Reputation: 2070

Query that Gets Transactions a user can take in SQLServer

I have a tblData and a tblUser.

I only want to display the transactions that the user can take.

The transactions are linked by a DisplayNum, if one of the Transactions in the DisplayNum does not match the TransType in the tblUser then the user cannot take any TransType in that DisplayNum.

enter image description here

Upvotes: 0

Views: 113

Answers (2)

jpw
jpw

Reputation: 44891

The final version put together after some chatting:

SELECT DISTINCT q2.Id, q3.SubQ, q1.DisplayNum, q1.TransType, q1.TotalTransTime, q1.UserId 
FROM (
    SELECT D.DisplayNum, HighestTransTime.TransType, SUM(D.TransTime) AS TotalTransTime, U.UserId 
    FROM tblData D
    INNER JOIN tblUser U ON D.TransType=U.TransType
    INNER JOIN 
        (
        SELECT DISTINCT innerQuery.DisplayNum, TransType 
        FROM tblData 
        INNER JOIN 
            (
            SELECT DisplayNum, MAX(TransTime) AS TransTime FROM tblData GROUP BY DisplayNum
            ) innerQuery ON tblData.DisplayNum = innerQuery.DisplayNum AND tblData.TransTime = innerQuery.TransTime
        ) HighestTransTime ON D.DisplayNum=HighestTransTime.DisplayNum 
    WHERE U.UserId = 10 
    AND D.TransType IN (SELECT TransType FROM tblUser WHERE tblUser.UserId = U.UserId)
    AND D.DisplayNum NOT IN (SELECT DisplayNum FROM tblData WHERE TransType NOT IN (SELECT TransType FROM tblUser WHERE tblUser.UserId = U.UserId))
    GROUP BY D.DisplayNum, HighestTransTime.TransType, U.UserId
    ) q1
INNER JOIN (SELECT DisplayNum, MAX(ID) AS ID FROM tblData GROUP BY DisplayNum) q2 ON q1.DisplayNum = q2.DisplayNum
INNER JOIN (SELECT SubQ, ID FROM tblData) q3 ON q2.ID=q3.ID
ORDER BY q2.ID

Upvotes: 1

dixpac
dixpac

Reputation: 533

As far I can understand you could use simple join on TransType column.

SELECT *
FROM dbo.tblData INNER JOIN dbo.tblUser ON 
dbo.tblData .TransType = dbo.tblUser .TransType

Upvotes: 0

Related Questions