Jump to content


Photo

SQL question. Is it possible to....


  • Please log in to reply
4 replies to this topic

#1 sbayeta

sbayeta
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 03 June 2003 - 04:14 PM

Hi,
I have a table with the fields \"id\", \"value\", \"value_on_nick\" and \"value_of_nick\", and some rows that look like:

|   id  | value | value_on_nick | value_off_nick |

|-------|-------|---------------|----------------|

| item1 |   0   |    abnormal   |      normal    |

| item2 |   1   |      cold     |       hot      |


Is it possible to create a query that will return the item id and the nick corresponding to the value ? Like


|   id  |  nick  |

|-------|--------|

| item1 | normal |

| item2 |  cold  |


Thanks!

#2 rhysmeister

rhysmeister
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts
  • LocationEngland

Posted 04 June 2003 - 09:06 AM

No sure if you mean something more complex, but if I understand...

SELECT id, nick from <tablename>;

#3 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 04 June 2003 - 10:23 AM

Yes... use a select in combination with IF....

check the syntax of the IF, then write if you need more help...


I.e. select ID, if (value =1, NICK_ON, nick_off) FROM TABLE....

The above is probably not the correct syntax, but you get the idea.

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#4 sbayeta

sbayeta
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 04 June 2003 - 03:46 PM

Thanks biopv. I found that yesterday, and it works fine. But doing some further researching, I discovered that there are ANSI SQL ways to do this:

SELECT CASE value WHEN 0 THEN offnick ELSE onnick END AS val;



#5 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 05 June 2003 - 07:38 AM

Lovely... I didn\'t know that...

Have fun,
P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users