Alias Question

kool_samule · #1 (**permalink**) 11-10-09, 11:15

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!

????

mike_bike_kite · #2 (**permalink**) 11-10-09, 11:55

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

r937 · #3 (**permalink**) 11-10-09, 20:24

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'

kool_samule · #4 (**permalink**) 11-12-09, 11:18

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.

r937 · #5 (**permalink**) 11-12-09, 11:23

Quote:

Originally Posted by kool_samule

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?

kool_samule · #6 (**permalink**) 11-12-09, 11:34

I think my brain has fried looking at code for too long . . .

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

thanks man, and sorry for the trouble!

Internet Services

Web Development

Digital Marketing

Consumer Tech