Error "Unknown column in where clause" - What is wrong?

Question

I'm using MySQL Server 5.7 and I have this query:

select 
    regDate,
    userID,
    t.teamID,
    login
from
    tbl_user u
inner join 
    tbl_team t on u.userID = t.userID
where
    regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
    AND 
    (
        select sum(transactions) from (
            SELECT count(*) as transactions FROM tbl_pp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
            union all
            SELECT count(*) as transactions FROM tbl_psc where (fromTeamID = t.teamID or toTeamID = t.teamID) and transactionDate >= u.regDate
            union all
            SELECT count(*) as transactions FROM tbl_mp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
        ) as all
    ) > 0

I'm getting this error:

Error Code: 1054. Unknown column 't.teamID' in 'where clause'

I'm sure this is just a minor issue, but I can't get it right now. And the column teamID is existing in table tbl_team. Anybody a hint for me?

Answer 1

You cannot nest correlated references more than one query deep. You are better off using exists anyway:

select u.regDate, u.userID, t.teamID, u.login
from tbl_user u inner join 
     tbl_team t
     on u.userID = t.userID
where u.regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH) and
      (exists (select 1
               from tbl_pp p
               where t.teamID in (p.fromTeamID, p.forTeamID) and
                     p.transactionDate >= u.regDate
              ) or
       exists (select 1
               from tbl_psc p
               where t.teamID in (p.fromTeamID, p.toTeamID) and
                     p.transactionDate >= u.regDate
              ) or
       exists (select 1
               from tbl_mp p
               where t.teamID in (p.fromTeamID, p.forTeamID) and
                     p.transactionDate >= u.regDate
              )
     )