Lists: | pgsql-general |
---|
From: | Jure Ložar <jure(dot)lozar(at)madalbal(dot)si> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | limit left join rows to 1 |
Date: | 2006-10-31 15:56:40 |
Message-ID: | 45477238.4050202@madalbal.si |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
Thank you
Jure
From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: limit left join rows to 1 |
Date: | 2006-10-31 17:14:23 |
Message-ID: | 20061031171422.GA13658@KanotixBox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Jure Ložar <jure(dot)lozar(at)madalbal(dot)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°
From: | Jure Ložar <jure(dot)lozar(at)madalbal(dot)si> |
---|---|
To: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: limit left join rows to 1 |
Date: | 2006-11-01 16:49:56 |
Message-ID: | 4548D034.1020209@madalbal.si |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Andreas Kretschmer wrote:
>Jure Ložar <jure(dot)lozar(at)madalbal(dot)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