Groups > comp.databases.postgresql > #335

Re: Joining two tables using two columns (instead of one)

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> (permalink)
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%m0YyyvgkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g\|E2#EhjKCa+nt":4b}dU7GYBHBxn&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

Show key headers only | View raw

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 ---

Thread

Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-05 15:21 +0200
  Re: Joining two tables using *two* columns (instead of one) Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-04-05 16:30 +0200
    Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-05 18:01 +0200
      Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-05 23:16 +0200
        Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-06 01:00 +0000
          Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-06 04:12 +0000
            Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-06 04:44 +0000
              Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-06 14:17 +0200
                Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-08 19:01 +0000
    Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-05 21:20 +0000
      Re: Joining two tables using *two* columns (instead of one) Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-04-06 09:30 +0200
        Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-06 23:42 +0000
        Re: Joining two tables using *two* columns (instead of one) Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-04-08 20:39 +0200
  Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-05 19:48 +0000
    Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-05 19:50 +0000
    Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-06 09:13 +0200
  Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-05 21:28 +0000
    Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-06 09:00 +0200
      Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-06 23:38 +0000
  Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-06 08:48 +0200
    Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-06 14:33 +0200
      Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-11 03:21 +0000
    Re: Joining two tables using *two* columns (instead of one) Torsten Kirschner <torsten.kirschner@gmail.com> - 2012-04-10 05:58 +0200
      Re: Joining two tables using *two* columns (instead of one) Fredrik Jonson <fredrik@jonson.org> - 2012-04-10 08:53 +0000
        Re: Joining two tables using *two* columns (instead of one) Torsten Kirschner <torsten.kirschner@gmail.com> - 2012-04-11 00:06 +0200
          Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-12 20:38 +0200

csiph-web