Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!news.netfront.net!reversiblemaps.ath.cx!not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: comp.databases.postgresql
Subject: Re: Joining two tables using *two* columns (instead of one)
Date: 5 Apr 2012 21:28:13 GMT
Organization: Dis (not Dat) Organisation
Lines: 30
Message-ID: <jll2pd$9p4$2@reversiblemaps.ath.cx>
References: <4f7d9c45$0$1745$426a34cc@news.free.fr>
NNTP-Posting-Host: 118.90.123.29
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Trace: adenine.netfront.net 1333661441 37667 118.90.123.29 (5 Apr 2012 21:30:41 GMT)
X-Complaints-To: news@netfront.net
NNTP-Posting-Date: Thu, 5 Apr 2012 21:30:41 +0000 (UTC)
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s;a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent: slrn/pre1.0.0-18 (Linux)
Xref: csiph.com comp.databases.postgresql:335

On 2012-04-05, Mateusz <no@spam.please> wrote:
> Hi,
>
> I'm stuck with a (maybe simple) problem of JOINing two tables.. The thing is that I need to join these tables on two different columns.
>
> Let's say I have a table called "subnet" with three columns: id, probe1, probe2. probe1 and probe2 are both foreign keys to a 'probes' table that contains columns: id, value. Now, I 
> need to join all this together...
>
> This is what I tried so far:
>
> SELECT id, prob1.value AS p1, prob2.value AS p2
>   FROM subnet
>   LEFT OUTER JOIN probes AS prob1 ON prob1.id=probe1
>   LEFT OUTER JOIN probes AS prob2 ON prob2.id=probe2
>   WHERE probe1 IS NOT NULL AND probe2 IS NOT NULL;
>
> This seemed good to me, because that's what I would do if I would need to join tables in the most simple way... Unfortunately the command doesn't produce anything - when I launch it, I 
> have to wait several minutes, only to get kicked out of the pgsql shell with a "out of memory" message. Of course if I join tables only once, I get the result immediately.
>
> I'm pretty sure I missed something obvious here... Any idea?

for each row of the subnet table you're asking for the cross product
of all the probe1 values with all the probe2 values

I can't really say any more without sample data.

-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---