>Hi,
>
>I am trying to limit LEFT JOIN to select only one record. By design the child table should have only one record but I ran into a case where user had two records (wrong but it happens). And when two records are selected the entire resulting query is wrong. So, for example, say I have the following query:
>
>

>select .... from Table1 left join Table2 on Table1.pk_field = Table2.pk_field
>

>
>How can I change (if possible) the ON expression so that if Table2 has more than one record that matches the Table1.pk_field, only one is selected? (It does not matter to me which one). TIA.

I think you can use something like this, in my example t2 is that table that has more records than what you want and t3 is just another table like you mention you have

CREATE CURSOR t1 (pk int autoinc, val C(10))
CREATE CURSOR t2 (pk int autoinc, val C(1), fkT1 int)
CREATE CURSOR t3 (pk int autoinc, val C(2), fkT1 int)

INSERT INTO t1 (val) values (SYS(2015))
INSERT INTO t1 (val) values (SYS(2015))
INSERT INTO t1 (val) values (SYS(2015))

INSERT INTO t2 (val, fkT1) VALUES ('A', 1)
INSERT INTO t2 (val, fkT1) VALUES ('B', 1)
INSERT INTO t2 (val, fkT1) VALUES ('A', 2)
INSERT INTO t2 (val, fkT1) VALUES ('A', 3)
INSERT INTO t2 (val, fkT1) VALUES ('B', 3)
INSERT INTO t2 (val, fkT1) VALUES ('C', 3)

INSERT INTO t3 (val, fkT1) VALUES ('AA', 1)
INSERT INTO t3 (val, fkT1) VALUES ('BB', 1)
INSERT INTO t3 (val, fkT1) VALUES ('AA', 2)
INSERT INTO t3 (val, fkT1) VALUES ('AA', 3)
INSERT INTO t3 (val, fkT1) VALUES ('BB', 3)
INSERT INTO t3 (val, fkT1) VALUES ('CC', 3)


SELECT * FROM T1 JOIN (select MIN(val) as val, fkT1 FROM t2 GROUP BY fkT1)  tt ON t1.pk = tt.fkt1 JOIN t3 ON t3.fkT1 = t1.pk