MYSQL: Can I use outer query table alias inside subquery

Question

Here is my query:

select uact.onAssetID as AssetID, a1.value as AssetValue,
uact.CommentID, a2.value from useractivity uact inner join asset a1 on
uact.onAssetID=a1.ID inner join (select * from asset inner join
useractivity on uact.onAssetID=a1.ID group by a1.ID limit 3) a2 on
uact.CommentID=a2.ID;

Error: ERROR 1054 (42S22): Unknown column 'uact.onassetID' in 'on clause'

What I am trying do? Imagine there is Table1 with 10 student names, now there is Table2 which has 10 records(rows) for each of the student present in table1. If I try to use join on both tables, it will return 100 records(10 records for each student). What I want is, to limit the result returned by join by 5 records per students So, I should get 5 records per student hence 50 output rows

Table Structure(asset):

ID | TypeID | CategoryID | Worth | isActive 
| CreationDate | ExpiryDate Value | AssetOwner 

Table Structure(useractivity) | ID | ActivityTypeID | UserID | Time | onAssetID | CommentID

Notes: 1) ID for both table is a primary key 2) onAssetID and commentID in useractivity are foriegn keys referring to ID in asset Table

Feel free to let me know if you need more details

Answer 1

What I am trying do? Imagine there is Table1 with 10 student names, now there is Table2 which has 10 records(rows) for each of the student present in table1. If I try to use join on both tables, it will return 100 records(10 records for each student). What I want is, to limit the result returned by join by 5 records per students So, I should get 5 records per student hence 50 output rows

Answer for this:- Table1(studentid,.......) Table2(id,.........,studentid)

select s.*,temp.* from (SELECT @var:=if(@var2= a.studentid,@var+1,1) sno, @var2:= studentid, a.*
FROM Table2 a,(select @var:=0) b, (select @var2:=0) c 
order by studentid) temp, Table1 s where temp.sno<=5;

For your table structures:- Table Structure(asset): ID | TypeID | CategoryID | Worth | isActive | CreationDate | ExpiryDate Value | AssetOwner Table Structure(useractivity): | ID | ActivityTypeID | UserID | Time | onAssetID | CommentID

select s.*,temp.* 
from 
     (SELECT @var:=if(@var2= a.onAssetID,@var+1,1) sno, @var2:= onAssetID, a.*
      FROM useractivity a,(select @var:=0) b, (select @var2:=0) c 
      order by onAssetID) temp, asset s 
where temp.sno<=5;