CODEWITHSUNDEEP
sql
Tom Gullen
Tom Gullen

Reputation: 61755

SQL Query Help, filtering by client ID

SELECT
    tblArtworkTemplates.ID,
    tblArtworkTemplates.userID as creatorID,
    tblArtworkTemplates.dateCreated,
    tblSpecifications.TxtPagination, 
    tblSpecifications.FlatSizeW AS width,
    tblSpecifications.FlatSizeL AS length,
    tblSpecifications.FlatSizeUOM AS uom,
    (SELECT COUNT(1) AS Expr1
        FROM tblArtworkUploads
        WHERE (templateID = tblArtworkTemplates.ID)) AS uploadCount,
    (SELECT COUNT(1) AS talks
        FROM tblArtworkTemplateMessages
        WHERE (templateID = tblArtworkTemplates.ID)) AS talkCount,
    tblUsers.id AS editUserID,
    tblUsers.userName,
    CAST((CASE WHEN DATEDIFF(n, tblArtworkTemplates.lastEditPing, getDate()) < 5 THEN 1 ELSE 0 END) AS bit) AS 'IsInLast5Mins'
FROM
    tblUsers RIGHT OUTER JOIN
    tblArtworkTemplates INNER JOIN
    tblSpecifications
        ON tblArtworkTemplates.specID = tblSpecifications.id
        ON tblUsers.id = tblArtworkTemplates.editPingUserID
WHERE
    (tblArtworkTemplates.userID = @userID)

This works fine for passing in the user ID to filter on. However, each user in tblUsers has a clientID. I want to instead of filtering by user ID, filter by client ID.

So I pass in where client ID = 21, and it returns a list of all the records where the user who created it has the client ID of 21.

I know this is an incredibly boring question and mind numbing but any help is hugely appreciated.

Edit: Table Structure

tblArtworkTemplates:
  - ID
  - userID (who created it)

tblUsers
  - ID
  - clientID

So instead of filtering on userID which is easy because that is stored in tblArtworkTemplates, I want to filter on client ID. So if I pass in client ID 21, it gets all artwork template records where the userID has that client ID.

Upvotes: 0

Views: 1246

Answers (1)

msarchet
msarchet

Reputation: 15242

SELECT
    tblArtworkTemplates.ID,
    tblArtworkTemplates.userID as creatorID,
    tblArtworkTemplates.dateCreated,
    tblSpecifications.TxtPagination, 
    tblSpecifications.FlatSizeW AS width,
    tblSpecifications.FlatSizeL AS length,
    tblSpecifications.FlatSizeUOM AS uom,
    (SELECT COUNT(1) AS Expr1
        FROM tblArtworkUploads
        WHERE (templateID = tblArtworkTemplates.ID)) AS uploadCount,
    (SELECT COUNT(1) AS talks
        FROM tblArtworkTemplateMessages
        WHERE (templateID = tblArtworkTemplates.ID)) AS talkCount,
    tblUsers.id AS editUserID,
    tblUsers.userName,
    CAST((CASE WHEN DATEDIFF(n, tblArtworkTemplates.lastEditPing, getDate()) < 5 THEN 1 ELSE 0 END) AS bit) AS 'IsInLast5Mins'
FROM
    tblUsers RIGHT OUTER JOIN
    tblArtworkTemplates INNER JOIN
    tblSpecifications
        ON tblArtworkTemplates.specID = tblSpecifications.id
        ON tblUsers.id = tblArtworkTemplates.editPingUserID
WHERE
    (tblArtworkTemplates.creatorID IN (Select ID From tblUsers 
     Where clientID = @clientID)

It seemed from your query that tblArtworkTemplates.editPingUserID was tblUsers.id

Upvotes: 1

Related Questions