Is there support for self joins?


#1

Did some experimenting but could not get it to work.

Got either a,

`Exception: Hash join failed, reason: Could not build a 1-to-1 correspondence for columns involved in equijoin`

or a,

`Exception: ERROR-- Validate failed: From line x, column x to line x, column x: Duplicate relation name 'x' in FROM clause`

Thanks


#2

Hi,

Yes we can support very limited self join.

eg:

mapdql> create table sjoin (jk text, v1 integer);
mapdql> insert into sjoin values ('t1',1);
mapdql> insert into sjoin values ('t2',2);
mapdql> select a.v1 + b.v1 total , a.jk key from sjoin a join sjoin b on a.jk = b.jk;
total|key
2|t1
4|t2

We currently insist on a star style schema so 1-to-1 relationships from fact to dimension table currently. So your limit with a self join is very limiting ie one row to itself.

We plan to expand this capability very soon to allow more flexibility on the join criteria.

regards


Not equal to operators