MYSQL: Can I use outer query table alias inside su

问题:

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

回答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;

回答2:

you call them like a2.column_name as you have called that query a2

回答3:

You should be able to do so, but only if you place it in the correct segment. And be aware that doing so in a FROM subquery could seriously hamper performance.

Anyway, your problem appears to be that you're referencing uact and a1 where they're not defined, which is a syntax violation. Add aliases to the subquery, and if needed reference the outer query in your WHERE clause.

Change

(
 SELECT * 
 FROM asset 
 INNER JOIN useractivity ON uact.onAssetID=a1.ID 
 GROUP BY a1.ID 
 LIMIT 3
) a2

to

(
 SELECT ID, Value 
 FROM asset AS a
 INNER JOIN useractivity AS ua 
   ON ua.onAssetID=a.ID 
) a2

(Note that your LIMIT clause would apply to the ENTIRE query, and a2 would only return three rows in total, rather than three rows per join.)