Groups > comp.databases.postgresql > #332

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

Path	csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail
From	Mladen Gogala <gogala.mladen@gmail.com>
Newsgroups	comp.databases.postgresql
Subject	Re: Joining two tables using two columns (instead of one)
Date	Thu, 5 Apr 2012 19:50:46 +0000 (UTC)
Organization	solani.org
Lines	32
Message-ID	<jlkt2m$qrd$2@solani.org> (permalink)
References	<4f7d9c45$0$1745$426a34cc@news.free.fr> <jlksv2$qrd$1@solani.org>
Mime-Version	1.0
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	8bit
X-Trace	solani.org 1333655446 27501 eJwFwYEBwCAIA7CXAGmVcyaV/09YgkVn7ySYGIwW6F942kU9Wu2wnL4KVcUDJRy2stI1x34LAxCq (5 Apr 2012 19:50:46 GMT)
X-Complaints-To	abuse@news.solani.org
NNTP-Posting-Date	Thu, 5 Apr 2012 19:50:46 +0000 (UTC)
User-Agent	Pan/0.135 (Tomorrow I'll Wake Up and Scald Myself with Tea; GIT 30dc37b master)
X-User-ID	eJwFwYEBwDAEBMCVIniMQ+v3HyF3rhB8YXCY02kMofIfdBRRkoAOT+zcXLTPPbZpcsuUXQ8fWxDn
Cancel-Lock	sha1:9DBqP6wVYxe5oUjUFqdy/FyclHA=
X-NNTP-Posting-Host	eJwFwQkBACAIA8BK8m0aB0H6R/AuDIKiI+AxMZJGnC1FNiofdiWi+5k+ZmVfuVhUXThD1w8jRxEz
Xref	csiph.com comp.databases.postgresql:332

Show key headers only | View raw

On Thu, 05 Apr 2012 19:48:50 +0000, Mladen Gogala wrote:

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

That can also be done with outer joins:



scott=# select d.deptno,d1.loc 
from dept d left outer 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)

I have never tried with joints, but then again....
-- 
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