Multi-level subquery in SQL Server

There are two tables, A and B.


Aid scriptid taskid yearID
1 5 3 1
2 5 4 1
3 5 5 1
4 6 3 1
5 6 4 1
6 7 3 2
7 7 5 2
8 8 2 2
9 8 6 2

Table B is as follows:
Bid aid
11 1
12 2
13 3
14 4
15 5
16 6
17 7
18 8
19 9

Obtain the maximum value of yearID = 1, scriptid = 5, and taskid not greater than 4 in table.

Select Scriptid, Max (Taskid) As Taskid From A
Where Ybid = 1   And Scriptid = 5   And Taskid <= 4
Group   By Scriptid

Only scriptid and taskid of a can be obtained above. If you want to obtain aid, you can use subquery

Select A1.id, a1.scriptid, a1.taskid
From A A1
Join
( Select Scriptid, Max (Taskid) As Taskid From A
Where Ybid = 1   And Scriptid = 5   And Taskid <= 4
Group   By Scriptid
) A2 On A1.scriptid = A2.scriptid And A1.taskid = A2.taskid

If you associate a's records that meet the preceding conditions with table B and obtain the ID of Table B, you can use multi-level subquery:

Select B. * ,. *   From B
Join  
( Select A1.id, a1.scriptid, a1.taskid
From A A1
Join
( Select Scriptid, Max (Taskid) As Taskid From A
Where Ybid = 1   And Scriptid = 5   And Taskid <= 4
Group   By Scriptid
) A2 On A1.scriptid = A2.scriptid And A1.taskid = A2.taskid
) On A. ID = B. Aid