SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Location
    USA
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:
    Code:
    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!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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'

  3. #3
    SitePoint Member
    Join Date
    Jan 2010
    Location
    USA
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works like a charm,thank you kindly!!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •