Nested SELECT with alias column

Hello world!
My first post and an sql novice so bare with my ignorance.
Goal/logic is to:

  1. find a record of a known/unique value in column ‘name’
  2. from this found record assign a variable/alias of a column ‘start_date’ just extracting the year as alias of ‘start_year’
  3. find all other records that are in the same year excluding original record in step 1
    eg.
    Look up name “Joe” and his start date being only the year.
    Find all other records that have the same start date as “Joe” but do not list Joe in the list returned.

Here is my attempt up to step 2:


SELECT
    name, FROM_UNIXTIME(start_date, '%Y') AS start_year
FROM
    db.table
WHERE
name='Joe' 
AND 
start_year IN (SELECT  name, FROM_UNIXTIME(start_date, '%Y') AS other_records_same_year FROM db.table WHERE other_records_same_year = start_year)

other_records_same_year - alias simply used to go around unix time.

…but apparently alias column can not be used in SELECT clause and any solution I could find doubled up on my SELECT statements and confused me even more.
If anyone could point me in the right direction I would appreciate it.
Hopefully the logic make sense.
Thank you for your time!


SELECT
    name
  , b.start_year
FROM db.table
INNER JOIN
  (SELECT
       FROM_UNIXTIME(start_date, '%Y') AS start_year 
   FROM db.table
   WHERE name = 'Joe' 
  ) AS b
ON FROM_UNIXTIME(start_date, '%Y') = b.start_year
WHERE name <> 'Joe'

Works like a charm,thank you kindly!!!