### Subqueries in the ON clause are not supported

Posted:

**Thu Dec 19, 2013 2:42 pm**Hi all,

I am trying to left join two tables and return the rows from t1 and matching rows in t2 but only those that have a maximum date.

As an example, I have these two tables:

Here's a simple left join of t1 and t2:

But I really want this result set:

I tried running the following query but got an error:

Next I tried the following query, but I lost data that is in t1 but not t2

Is there a work around for this?

I am trying to left join two tables and return the rows from t1 and matching rows in t2 but only those that have a maximum date.

As an example, I have these two tables:

Code: Select all

```
dbadmin=> select * from t1;
c1
-------
data2
data1
(2 rows)
```

Code: Select all

```
dbadmin=> select * from t2;
c1 | c2 | c3
-------+----+------------
data1 | 1 | 2013-12-19
data1 | 2 | 2013-12-20
(2 rows)
```

Code: Select all

```
dbadmin=> select * from t1 left join t2 on t2.c1 = t1.c1;
c1 | c1 | c2
-------+-------+------------
data2 | |
data1 | data1 | 2013-12-19
data1 | data1 | 2013-12-19
(3 rows)
```

Code: Select all

```
c1 | c2 | c3
-------+----+------------
data1 | 2 | 2013-12-20
data2 | |
```

Code: Select all

```
dbadmin=> select t1.c1, t2.c2, t2.c3 from t1 left join t2 on t2.c1 = t1.c1 and t2.c3 = (select max(t3.c3) from t2 t3 where t3.c1 = t2.c1) order by t1.c1;
ERROR 4816: Subqueries in the ON clause are not supported
```

Code: Select all

```
dbadmin=> select t1.c1, t2.c2, t2.c3 from t1 left join t2 on t2.c1 = t1.c1 where t2.c3 = (select max(t3.c3) from t2 t3 where t3.c1 = t2.c1) order by t1.c1;
c1 | c2 | c3
-------+----+------------
data1 | 2 | 2013-12-20
(1 row)
```