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

Groups > comp.databases.postgresql > #344

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

Show all headers | View raw

On 06.04.2012 08:48, Mateusz wrote:

> And that's exactly what I can't understand: why is it soo harder for
> the SQL engine to perform the 2xJOINs requests than it is to perform
> 2 times a single JOIN request? In both cases I get the data I need.
> This makes me think that I missed something obvious, and that my
> first version (2xJOINs) is doing much more work than what I need it
> to do...

Then please do "explain select ..." or even "explain analyze select ..." 
and post results to http://explain.depesz.com/ and refer them here.

> My fallback option would be to create a temporary table, and then
> populate it with the result of the two SELECTs above (done one after
> the other, possibly inside a single transaction), and then GROUP BY
> all entries to get my data... But is there really no way to perform
> the same job with one single magic SQL incantation?

Well, you proved that you *can* execute it with a single SELECT.  It's 
just not as quick (yet) as you expect.  So you need to find out what's 
wrong.

Btw, since there a foreign key on the subnet.probe1 and subnet.probe2 
you can (and probably should) use a regular join.  No need for an outer 
join because you know that every occurrence of subnet.probe1 and 
subnet.probe2 has an entry in probes.  So the query would be

SELECT subnet.id,val1.value,val2.value
   FROM subnet
   JOIN probes AS val1 ON subnet.probe1 = val1.id
   JOIN probes AS val2 ON subnet.probe2 = val2.id

Kind regards

	robert

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