When doing OUTER JOIN to a table, how can I join the other table to the one on the right side of the outer join?

I have an ASP.NET Sql Server 2012 application and I am trying to create a report from four tables with SQL. I have a problem getting data for one field (status).

I have four tables:

• AdminTest - available tests
• AdminTestQuestions - Test Questions
• UserTest - Checks what the user has purchased
• UserTestStatus is a lookup table (with two columns: UserTestStatusId and name)

What I would like to do is create a report like this which shows an example ABC test that has antry in the AdminTest and UserTest tables, and a test DEF that only has an entry in the AdminTest table:

Code PurchaseDate  Questions Status
ABC  2012/1/1      50        Available
DEF                25        Purchase Now  

      

        
        
        
      

    

Initially there would be only AdminTest lines and no access to UserTest. This is ok with LEFT OUTER JOIN. When a user buys a test, there will be an entry on the day of purchase and I can join the UserTest and UserTestStatus columns.

If there is no custom test, I would like the status to be "Buy now"

Here's what I have so far. It only works if there is an AdminTest and UserTest table for the specific test code.

SELECT   AdminTest.Code        AS Code,     
         UserTest.PurchaseDate AS PurchaseDate,
         COUNT(AdminTestQuestion.AdminTestQuestionId) AS Questions,
         UserTestStatus.Name   AS Status
FROM     AdminTest LEFT OUTER JOIN UserTest ON  AdminTest.AdminTestId = UserTest.AdminTestId 
                                            AND UserTest.UserId = @UserId
JOIN     AdminTestQuestion ON  AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
                           AND AdminTest.ExamId = @ExamId
                           AND AdminTest.TestStatusId = 3
JOIN     UserTestStatus    ON  UserTest.TestStatusId = UserTestStatus.UserTestStatusId 
GROUP BY AdminTest.Code,
     UserTest.PurchaseDate, 
     UserTestStatus.Name

      

        
        
        
      

    

So the connections should look like this:

AdminTest >>> AdminTestQuestions
          >>> UserTest (optional) >>> UserTestStatus 

      

        
        
        
      

    

Can anyone give me some advice as to how I can get the status data equal to the words " Purchase Now

" or be the actual status if purchased UserTest

.

Update:

Here is the answer that was suggested. I've included this as well as the output of the three selected below. Hope this helps to show the data. I can add something else to show more data if needed.

Select 1:

SELECT Code, AdminTestId 
FROM AdminTest

      

        
        
        
      

    

Output

Code    AdminTestId
abcdef  131
ddddddd 1130

      

        
        
        
      

    

Select 2:

SELECT AdminTestId, UserTestId 
FROM UserTest

      

        
        
        
      

    

Output

AdminTestId UserTestId
131           202

      

        
        
        
      

    

Choose 3:

SELECT AdminTestQuestionId, AdmintestId, QuestionUId
FROM AdminTestQuestion

      

        
        
        
      

    

Output

3094    131     3CEFF956-BF61-419E-8FB2-9D6A1B75B909
4094    1130    5D679FAD-2904-45A1-AA5F-3DB16850438D
4095    1130    96EA8351-FA2B-4DB3-862D-260CA085563A

      

        
        
        
      

    

Select 4:

SELECT * 
FROM UserTestStatus

      

        
        
        
      

    

Output:

0   Available
1   Started
2   Paused
3   Finished
4   Marked

      

        
        
        
      

    

Decision:

SELECT   temp.Code,     
         temp.Questions,
         UserTest.PurchaseDate AS PurchaseDate,
         (CASE 
              WHEN UserTestStatus.Name  IS NULL THEN 'Purchase Now'
              ELSE UserTestStatus.Name
         END)  AS Status
FROM
(SELECT   AdminTest.Code        AS Code,  
          AdminTest.AdminTestId,   
          COUNT(AdminTestQuestion.AdminTestQuestionId) AS Questions
 FROM     AdminTest 
 JOIN     AdminTestQuestion ON  AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
                           AND AdminTest.TestStatusId = 3
 GROUP BY AdminTest.Code, AdminTest.AdminTestId) temp
LEFT OUTER JOIN UserTest       ON  temp.AdminTestId = UserTest.AdminTestId 
LEFT OUTER JOIN UserTestStatus ON  UserTest.TestStatusId = UserTestStatus.UserTestStatusId

      

        
        
        
      

    

Here's what I need to see:

Code    PurchaseDate            Questions Status
abcdef  2014-10-17 15:27:31.760    1      Available
ddddddd null                       1     Purchase Now