Groups > comp.databases.postgresql > #348

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

From	Lennart Jonsson <erik.lennart.jonsson@gmail.com>
Newsgroups	comp.databases.postgresql
Subject	Re: Joining two tables using two columns (instead of one)
Date	2012-04-08 20:39 +0200
Organization	A noiseless patient Spider
Message-ID	<jlsm0e$pq4$1@dont-email.me> (permalink)
References	<4f7d9c45$0$1745$426a34cc@news.free.fr> <jlkaa5$9ii$1@dont-email.me> <jll2b9$9p4$1@reversiblemaps.ath.cx> <jlm62u$tv7$1@dont-email.me> <jlntnr$4vg$4@reversiblemaps.ath.cx>

Show all headers | View raw

On 2012-04-07 01:20, Jasen Betts wrote:
> On 2012-04-06, Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:
>> On 2012-04-05 23:20, Jasen Betts wrote:
>>> On 2012-04-05, Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:
>>>>> 
>>>>> 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;
>>>>
>>>> Without giving it to much thought, this should be equal with:
>>>>
>>>> SELECT id, prob1.value AS p1, prob2.value AS p2
>>>> FROM subnet
>>>> JOIN probes AS prob1
>>>>     ON  prob1.id=probe1
>>>> JOIN probes AS prob2
>>>>     ON prob2.id=probe2;
>>> 
>>> No, that query may return different results, it won't return rows 
>>> where subnet.probe1 or subnet.probe2 have do not have a matches in 
>>> the probes table
>>> 
>>
>> WHERE probe1 IS NOT NULL AND probe2 IS NOT NULL;
>>
> 
> that's looking at the subnet table not at the probes table.

Ah, you're right. I wrongly assumed the other way around since the where
clause does not make any sense as is.

/Lennart

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