limit left join rows to 1

Jure Loï¿œar <jure.lozar@madalbal.si> schrieb:

Hi.

Is it possible to limit number of left join rows that match condition to 1?
I don't want to have hits from first table multiplied when more then 1 row
matches on left join condition.

I'm not sure if i understand you correctly, but perhaps this is what you
are searching for:

Suppose, you have 2 tables, master and detail:

test=# select * from master;
id
----
1
2
(2 rows)

test=# select * from detail;
id | val
----+-----
1 | 200
2 | 200
1 | 100
(3 rows)

This is the left join:

test=# select m.id, d.val from master m left join detail d on m.id=d.id;
id | val
----+-----
1 | 100
1 | 200
2 | 200
(3 rows)

But you need only one row from detail, which? Suppose, this one with the
max(val) value:

test=# select m.id, d.val from master m left join (select id, max(val)
as val from detail group by id) d on m.id=d.id;
id | val
----+-----
1 | 200
2 | 200
(2 rows)

Is this okay for you?

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082ￜ, E 13.56889ￜ

Andreas Kretschmer wrote:

Jure Loï¿œar <jure.lozar@madalbal.si> schrieb:

Hi.

Is it possible to limit number of left join rows that match condition to 1?
I don't want to have hits from first table multiplied when more then 1 row
matches on left join condition.

I'm not sure if i understand you correctly, but perhaps this is what you
are searching for:

Suppose, you have 2 tables, master and detail:

test=# select * from master;
id
----
1
2
(2 rows)

test=# select * from detail;
id | val
----+-----
1 | 200
2 | 200
1 | 100
(3 rows)

This is the left join:

test=# select m.id, d.val from master m left join detail d on m.id=d.id;
id | val
----+-----
1 | 100
1 | 200
2 | 200
(3 rows)

But you need only one row from detail, which? Suppose, this one with the
max(val) value:

test=# select m.id, d.val from master m left join (select id, max(val)
as val from detail group by id) d on m.id=d.id;
id | val
----+-----
1 | 200
2 | 200
(2 rows)

Is this okay for you?

Andreas

Yes. It's good. Not exactly what I ment but it works.

Thank you.

Jure