MySQL - Alias Question

[Guest]

Hi Chaps, 

Have a quick one for you...

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

...tbl_jobxml.job1 as job2

but can you create an alias for the value within that column, eg.

...tbl_jobxml.job1='y' as job2='Complete'

Sorry if this is dumb question, but better to ask that claim ignorance!

????

[The_FedEx_Guy · Accepted solution]

I dont think that will work, as you are telling the query to find "y" in the field.

If the field had "y" input as a value it will work fine.

But I dont think an alias will work on value.

[David_Powers]

littlemookie wrote:

but can you create an alias for the value within that column, eg.

...tbl_jobxml.job1='y' as job2='Complete'

In a word, No.

It might be better if you said what you're trying to achieve.

[bregent]

Sounds like you are looking for a case statement. I'm not sure if MySQL support those in select statements, or only in sprocs. Many DBMS's do support this.

OK, found this on the web:

SELECT (CASE field1 WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) FROM mytable;

Message was edited by: bregent

[Guest]

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.

[bregent]

You would just add those conditions to the where clause:

where tableA.projectstatus='complete' and tableB.projectstatus='complete' and tableC.jobstatus='y'