informix 14.10 How to join three table for double mapping

Question

I have 3 tables c1,map1,map2 like this:

 --   t1             map1      map2
--  c1  cm  cn      m1  m2    n1  n2
     1  1    1      1   a     1   x 
     2  1    1      2   b     2   y 
     3  2    1
     4  1    2
     5  2    2

And the output should be such a table:

  1    a    x
  2    a    x
  3    b    x
  4    a    y
  5    b    y

If it was just table map1, the problem was simple

select c1,m1 from t1 cross join map1 where t1.c1=map1.m1

Can anybody help?

Answer 1

On the face of it, this should do the job:

SELECT t1.c1, m1.m2, m2.n2
  FROM t1
  JOIN map1 AS m1 ON t1.cm = m1.m1
  JOIN map2 AS m2 ON t1.cn = m2.n1

Tested with this SQL to confirm the result:

CREATE TEMP TABLE t1(c1 INTEGER NOT NULL PRIMARY KEY, cm INTEGER NOT NULL, cn INTEGER NOT NULL);
CREATE TEMP TABLE map1(m1 INTEGER NOT NULL PRIMARY KEY, m2 CHAR(1) NOT NULL);
CREATE TEMP TABLE map2(n1 INTEGER NOT NULL PRIMARY KEY, n2 CHAR(1) NOT NULL);
INSERT INTO t1 VALUES(1, 1, 1); 
INSERT INTO t1 VALUES(2, 1, 1); 
INSERT INTO t1 VALUES(3, 2, 1); 
INSERT INTO t1 VALUES(4, 1, 2); 
INSERT INTO t1 VALUES(5, 2, 2); 
INSERT INTO map1 VALUES(1, 'a');
INSERT INTO map1 VALUES(2, 'b');
INSERT INTO map2 VALUES(1, 'x');
INSERT INTO map2 VALUES(2, 'y');

SELECT t1.c1, m1.m2, m2.n2
  FROM t1
  JOIN map1 AS m1 ON t1.cm = m1.m1
  JOIN map2 AS m2 ON t1.cn = m2.n1;

Output:

c1  m2  n2
1   a   x
2   a   x
3   b   x
4   a   y
5   b   y