Re: Fine tuning my queries !

On Jun 26, 11:48 pm, Zeba <coolz...@xxxxxxxxx> 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
-----------------------
aaaa & bbbb
.

Relevant Pages

  • Fine tuning my queries !
    ... pool id, ... Pool name is always either suffixed by intl or core. ... suffix. ...
    (comp.databases.oracle.server)
  • Re: Parallel processing and APC synchronization.
    ... it's possible to specify max number of threads (from the pool) to be active ... case of Nehalem 2 threads per core. ... active work requests for completion port, ... PostQueuedCompletionStatus API. ...
    (microsoft.public.win32.programmer.kernel)