Fine tuning my queries !

Hi,

My db has three columns : pool id, pool name and pool description 1. Pool id is unique
2. Pool name is always either suffixed by intl or core. But there is not formal constraint or foreign key relationships anywhere

Given the pool id, I want to get back the pool descriptions for both the core and intl versions of that pool name.

e.g.

Pool id             Pool name             Pool desc
1                   abc-core              aaaa
2.                  abc-intl              bbbb
3.                  xyz-core              cccc
4.                  xyz-intl              dddd

So if i'm given Pool id=1, I should get back aaaa & bbbb. If i'm given pool id=4, I should get back dddd & cccc.

Is it possible/better to do this entirely in sql?

Or do I have to do this in two steps at backend code like this: 1. Find pool name for given pool id. Trim this of the core/intl suffix.
2. Find the pool desc. for the pool name containing above text ( but what if I have pool names that are like somename-core and somenamepluscore  ?!)

Please help !

Thanks,
Zeba Received on Fri Jun 27 2008 - 01:48:14 CDT