How can I change the LEFT OUTER JOIN to add a filter for the right side table

I have three tables in my database.

• AdminTest - contains a list of tests available to users
• AdminTestQuestion - contains a list of questions
• UserTest . List of checks that users have purchased. There's a UserId column in that table and rows in the table always matter for that. When making a selection, I need to be able to filter the rows in this table using the UserId

The data looks like this:

• The database stores the results of three tests. test1, test2 and test3
• Person with userId = 1 purchased test2
• The person with userId = 2 purchased test3.

I am using the following SQL:

SELECT 
    AdminTest.AdminTestId,
    AdminTest.Title,
    COUNT(AdminTestQuestion.AdminTestQuestionId) Questions,
    AdminTest.Price,
    UserTest.PurchaseDate
FROM AdminTest
    LEFT OUTER JOIN UserTest
        ON AdminTest.AdminTestId = UserTest.AdminTestId
    JOIN AdminTestQuestion
        ON AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
GROUP BY 
    AdminTest.AdminTestId,
    AdminTest.Title,
    UserTest.UserId

      

        
        
        
      

    

Which gives me a report like this:

AdminTestId  Title    Questions     Price     PurchaseDate
1            Test1    10            0
2            Test2    20            0         1/1/2011
3            Test3    10            10        2/2/2012

      

        
        
        
      

    

Can someone suggest how I could change this so that SQL UserId

takes a parameter so that it can correctly show tests that were purchased by a specific user:

This is what I would like to see when I provide a value 1

for the UserId parameter:

AdminTestId  Title    Questions     Price     PurchaseDate
1            Test1    10            0
2            Test2    20            0         1/1/2011  
3            Test3    10            10     

      

        
        
        
      

    

This is what I would like to see when I provide a value 2

for the UserId parameter:

AdminTestId  Title    Questions     Price     PurchaseDate
1            Test1    10            0
2            Test2    20            0         
3            Test3    10            10        2/2/2012

      

        
        
        
      

    

What I have tried so far is to add WHERE clauses with UserId to the AdminUser part for selection. But that doesn't seem to work. Hopefully someone can point me in the right direction.

For reference, here's the DDL of the UserTest table that I want to filter somehow with the UserId:

CREATE TABLE [dbo].[UserTest] (
    [UserTestId]              INT           IDENTITY (1, 1) NOT NULL,
    [AdminTestId]             INT           NOT NULL,
    [UserId]                  INT           NOT NULL,
    [PurchaseDate]            DATETIME      NOT NULL,
    CONSTRAINT [PK_UserTest] PRIMARY KEY CLUSTERED ([UserTestId] ASC)
);