How can I modify a LEFT OUTER JOIN to add a filter for the RIGHT side table

Question

I have three tables in my database.

• AdminTest - Holds a list of tests that are available for users
• AdminTestQuestion - Holds a list of questions
• UserTest - Holds a list of tests that users have purchased. There's a UserId column in this table and rows in the table always have a value for this. When doing a select I need to be able to filter out the rows in this table by UserId

The data looks like this:

• The database stores the results of three tests. test1, test2 and test3
• The 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 modify this so the SQL takes a parameter of UserId so it could correctly show the tests that have been purchased by a particular user:

This is what I would like to see when I provide a value of 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 of 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 adding WHERE clauses with the UserId to the AdminUser part of the select. But this does not seem to work. I hope someone can point me in the right direction.

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

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)
);

Answer 1

To expand on what @RichardHansell said...

You can filter a JOIN by adding things to the 'ON' clause of the script. The ON clause does not have to be only links between the two tables, you can add other filters in as well. Like so...

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

Answer 2

If you put the parameter test in the ON clause of the Left Outer Join, you should get the results you're after:

...
LEFT OUTER JOIN UserTest
ON       AdminTest.AdminTestId = UserTest.AdminTestId
AND      UserTest.UserId = @UserId
...