Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #331

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

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

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