Vitali Lovich wrote:
The solution I came up with is:
SELECT coalesce(
(SELECT field1 FROM tbl1 WHERE key = $key),
(SELECT field1 FROM tbl2 WHERE key = $key),
(SELECT field1 FROM tbl3 WHERE key = $key))
, field2 FROM tbl1 WHERE key = $key;
However, if
coalesce works the way I think it does, then it'll do early evaluation
and stop at the first non null parameter.
Vitali,
Coalesce does not work the way you suggest. It is an SQL function and
like all functions in SQLite it is passed all its arguments during the
call. So SQLite has to evaluate all the select expressions to generate
the arguments before calling coalesce. The coalesce function simply
returns the first non null argument.
You could combine the tables like this:
select field1 from
(
select 1 as tbl, field1 from tbl1 where key = :key and field1
not null
union
select 2 as tbl, field1 from tbl2 where key = :key and field1
not null
union
select 3 as tbl, field1 from tbl3 where key = :key and field1
not null
)
order by tbl limit 1
Or you could use a case expression to execute the lookups only if
needed, but at the expense of repeating the succesful lookup twice.
select
case
when (select field1 from tbl1 where key = :key) not null
then (select field1 from tbl1 where key = :key)
else
case
when (select field1 from tbl2 where key = :key) not null
then (select field1 from tbl2 where key = :key)
else
case
when (select field1 from tbl3 where key = :key) not null
then (select field1 from tbl3 where key = :key)
else null
end
end
end
as field1;
This could be optimized somewhat if SQLite supported the WITH clause for
named subexpressions. This would allow the lookup queries to be executed
only once to build an internal temporary table. If the optimizer is
smart enough, it could delay the execution of the temp table creation
until the temp table is referenced. This would do the lookups only once,
and only if needed.
with
tbl1_f1 (field1) as (select field1 from tbl1 where key = :key)
tbl2_f1 (field1) as (select field1 from tbl2 where key = :key)
tbl3_f1 (field1) as (select field1 from tbl3 where key = :key)
select
case
when (select field1 from tbl1_f1) not null
then (select field1 from tbl1_f1)
else
case
when (select field1 from tbl2_f1) not null
then (select field1 from tbl2_f1)
else
case
when (select field1 from tbl3_f1) not null
then (select field1 from tbl3_f1)
else null
end
end
end
as field1;
The question that I have to ask is why is the data separated into these
three tables? Your life would be a whole lot easier if you combined the
three tables into one with an additional column that indicated the
source of the original data. Instead of this:
create table tbl1 (key primary key, field1);
create table tbl2 (key primary key, field1);
create table tbl3 (key primary key, field1);
You could do this:
create table tbl_all (tbl, key, field1, primary key(tbl, key));
insert into tbl_all
select 1 as tbl, key, field1 from tbl1
union
select 2 as tbl, key, field1 from tbl2
union
select 3 as tbl, key, field1 from tbl3
Then your query becomes
select field1 from tbl_all
where key = :key and field1 not null
order by tbl limit 1
If you don't want to create an actual table with the combined data you
could get much the same effect using a view.
create view view_all as
select 1 as tbl, key, field1 from tbl1
union
select 2 as tbl, key, field1 from tbl2
union
select 3 as tbl, key, field1 from tbl3
select field1 from view_all
where key = :key and field1 not null
order by tbl limit 1
The drawback to this approach is that it will actually build the entire
table defined by the view as a temporary table before executing a query
that references the view.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
