Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.postgresql Subject: Re: Joining two tables using *two* columns (instead of one) Date: Thu, 5 Apr 2012 19:48:50 +0000 (UTC) Organization: solani.org Lines: 53 Message-ID: References: <4f7d9c45$0$1745$426a34cc@news.free.fr> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: solani.org 1333655330 27501 eJwFwQkBwDAIA0BLfIEhp6HFv4TdwVNzKhIZWKyfb4dLU9KRB3LriXdXW7Fq1K8ETzyfNYsfJqgRJg== (5 Apr 2012 19:48:50 GMT) X-Complaints-To: abuse@news.solani.org NNTP-Posting-Date: Thu, 5 Apr 2012 19:48:50 +0000 (UTC) User-Agent: Pan/0.135 (Tomorrow I'll Wake Up and Scald Myself with Tea; GIT 30dc37b master) X-User-ID: eJwFwYEBwCAIA7CXhEo7z0GQ/09YEqCxtBncMTG+LA/7wqHmMZQc4xT8wbSolbzf68na6vkB/68QnQ== Cancel-Lock: sha1:4lgOf3kcdO2ptOUVgEsvk/vpsdw= X-NNTP-Posting-Host: eJwFwQEBwDAIAzBNvJRROQOGfwlPiLDo48FwLtfi2tub8HZ8Zb4adc0BV3xYnpFyjAWk5gcgdBEd Xref: csiph.com comp.databases.postgresql:331 On Thu, 05 Apr 2012 15:21:08 +0200, Mateusz 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? > > best regards, > Mateusz You are allowed to compare ordered pairs. That's the relational theory: scott=# create table dept1 as select * from dept scott-# ; SELECT 4 scott=# select d.deptno,d1.loc scott-# from dept d join dept1 d1 on ((d.deptno,d.loc)= (d1.deptno,d1.loc)); deptno | loc --------+---------- 10 | NEW YORK 20 | DALLAS 30 | CHICAGO 40 | BOSTON (4 rows) scott=# -- http://mgogala.byethost5.com