using outside alias to subquery

[Kev Loip]

I'm currently stuck here and don't know what to do next since I can't use the outside alias into the subquery. Here's my code (I commented the part that have a problem). Any help will be appreciated. Thanks.

SELECT tv_main.id,
tv_main.vesselName,
(
SELECT SUM(t_statCtr.status = 'EX CREW')
FROM
(
(
SELECT tpi_stat.id,
tvv.vesselName,
lastname,
firstname,
middlename,
IF(tpi_stat.returningCrew = 1, 'NEW HIRE',
IF(COUNT(tc_ctr.personnel_id) > 1, 'EX CREW', 'NEW HIRE')
)
AS status
FROM tbl_contracts AS tc_stat
LEFT JOIN tbl_personnel_info AS tpi_stat
ON tpi_stat.id = tc_stat.personnel_id

LEFT JOIN tbl_contracts AS tc_ctr
ON tpi_stat.id = tc_ctr.personnel_id

LEFT JOIN tbl_vessels AS tvv
ON tvv.id = tpi_stat.lastJoinedVsl

WHERE
tpi_stat.emp_status = 'ON-BOARD'
AND tc_stat.status = 'ACTIVE'
AND tvv.id = tv_main.id --This line have an error, (Unknown Column tv_main.id in where clause)
GROUP BY tc_stat.personnel_id
) AS t_statCtr
)
) AS ex_crew,
NULL AS new_hire
FROM tbl_vessels AS tv_main -- I need this one to use inside the subquery
LEFT JOIN tbl_personnel_info AS tpi
ON tv_main.id = tpi.lastJoinedVsl

LEFT JOIN tbl_contracts AS tc
ON tpi.id = tc.personnel_id
GROUP BY tv_main.vesselName