Results 1 to 6 of 6

Thread: Alias Question

  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Alias Question

    Hi Chaps,

    Have a quick one for you...

    In a SQL Query, I know you can create an alias for column name, eg.

    Code:
    ...tbl_jobxml.job1 as job2
    but can you create an alias for the value within that column, eg.

    Code:
    ...tbl_jobxml.job1='y' as job2='Complete'
    Sorry if this is dumb question, but better to ask that claim ignorance!

    ????

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Not really sure what you're asking but do you mean this:
    Code:
    select 'y' as job1, 'Complete' as job2;
    or do you mean translating the value 'y' to become 'Complete'?
    Can you give a clearer example?

    Mike

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not sure what you're asking either...

    maybe it is whether you can refer to that alias in the WHERE clause? the answer is no

    this is not allowed --
    Code:
    SELECT some_complicated_expression AS my_alias
      FROM ...
     WHERE my_alias = 'some value'
    here, the complicated expression might involve functions, calculations, and so on, and the intention is to return only rows where the expression evaluates to some specific value

    unfortunately you can't use the alias in the WHERE clause (which is why you see some people using HAVING instead, where it is allowed, but without a GROUP BY, which is an abomination if you ask me, but that's a different thread)

    of course, repeating the complicated expression in the WHERE clause works, but it's rather ungainly

    the easy way to get around this limitation is to push the expression down into a subquery --
    Code:
    SELECT my_alias
      FROM ( SELECT some_complicated_expression AS my_alias
             FROM ... ) AS derived_table
     WHERE my_alias = 'some value'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2009
    Posts
    4
    Hi Guys,

    sorry to the delay in responding, basically this is what I want to do:

    I want to show all results when:

    - tableA
    projectstatus='complete'

    - tableB
    projectstatus='complete'

    - tableC
    jobstatus='y'

    I'm familiar with UNIONs and INNER JOINs, it's just not sure how to go about getting all the results form the 3 tables, when the values are different.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kool_samule View Post
    I want to show all results when:

    - tableA
    projectstatus='complete'

    - tableB
    projectstatus='complete'

    - tableC
    jobstatus='y'
    okay, here's what the query will look like ...
    Code:
    SELECT ...
      FROM ...
     WHERE tableA.projectstatus = 'complete'
       AND tableB.projectstatus = 'complete'
       AND tableC.jobstatus = 'y'
    sweet, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2009
    Posts
    4
    I think my brain has fried looking at code for too long . . .

    I knew that! . . .well simple. . . ..

    thanks man, and sorry for the trouble!

Posting Permissions

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