Re: Fine tuning my queries !

On Jun 26, 11:48 pm, Zeba <coolz..._at_gmail.com> wrote:
> 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 somenameplus-
> core ?!)
>
> Please help !
>
> Thanks,
> Zeba

Possible? Yes. Nice? Not so much. I think the table design is not optimal for what you want.

SQL> create table t ( pool_id integer, pool_name varchar2(10), pool_description varchar2(10) );
Table created

SQL> alter table t add constraint t_pk primary key ( pool_id ); Table altered

SQL> insert into t values ( 1, 'abc-core', 'aaaa' ); 1 row inserted

SQL> insert into t values ( 2, 'abc-intl', 'bbbb' ); 1 row inserted

SQL> insert into t values ( 3, 'xyz-core', 'cccc' ); 1 row inserted

SQL> insert into t values ( 4, 'xyz-intl', 'dddd' ); 1 row inserted

SELECT t.pool_description || ' & ' ||alt.pool_description as both_descriptions
FROM t, ( SELECT pool_name, pool_description FROM t ) alt WHERE pool_id = 1
AND replace(replace(t.pool_name, '-core'), '-intl' ) = replace(replace(alt.pool_name, '-core'), '-intl' ) AND t.pool_name != alt.pool_name
/

BOTH_DESCRIPTIONS