Hello world!
My first post and an sql novice so bare with my ignorance.
Goal/logic is to:
- find a record of a known/unique value in column ‘name’
- from this found record assign a variable/alias of a column ‘start_date’ just extracting the year as alias of ‘start_year’
- 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!