Works for me:
create table d1( id bigint, name varchar(50), archived
smallint);
create table d2(id bigint, category varchar(60), source
varchar(100), translation varchar(500));
create index idx_d1_id on d1(id);
create index idx_d2_id on d2(source);
explain
select d.id, ifnull(translation,name)
from d1 d left join d2 on source=name and category='desc'
where archived=0
order by d.id;
gives me:
SELECT
D.ID,
IFNULL(TRANSLATION, NAME)
FROM PUBLIC.D1 D
/* PUBLIC.IDX_D1_ID */
/* WHERE ARCHIVED = 0
*/
LEFT OUTER JOIN PUBLIC.D2
/* PUBLIC.IDX_D2_ID: SOURCE = NAME */
ON (CATEGORY = 'desc')
AND (SOURCE = NAME)
WHERE ARCHIVED = 0
ORDER BY 1
/* index sorted */
On 2013-07-05 11:22, Alex wrote:
select d1.id ,ifnull(translation,name) from d1 d
left join d2 on source=name and category='desc'
where archived=0 order by sort_order,d1.id;
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
