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

Groups > comp.databases.postgresql > #328 > unrolled thread

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

Back to article view | Back to comp.databases.postgresql

Contents

  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

Page 1 of 2  [1] 2  Next page →

#328 — Joining two tables using *two* columns (instead of one)

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

[toc] | [next] | [standalone]

#329

On 04/05/2012 03:21 PM, 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;

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;

With proper indexes there's a good chance that this will be less
resource demanding


/Lennart

[toc] | [prev] | [next] | [standalone]

#330

Hi,

I see that you replaced my "LEFT OUTER JOIN" by short "JOIN" - I tested that, but it doesn't change (in any noticeable way at least) the behavior of the database.. I still have to 
wait several minutes, to end up with a "out of memory" message.

If I perform such request:
SELECT subnet.id, probes.value
   FROM subnet
   LEFT OUTER JOIN probes ON probes.id=subnet.probe1;

I get the result after less than 1 second. So doing it on 2 columns should (I guess) still be well under 2s... I don't understand why adding a second column is so much different from 
doing the request two times?

best regards,
Mateusz





Lennart Jonsson wrote:
> On 04/05/2012 03:21 PM, 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;
> 
> 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;
> 
> With proper indexes there's a good chance that this will be less
> resource demanding
> 
> 
> /Lennart

[toc] | [prev] | [next] | [standalone]

#333

On 04/05/2012 06:01 PM, Mateusz wrote:

Btw, the subject does not match your problem description well: I would 
consider a join on two columns as a join where there are criteria on two 
columns.  You are doing two joins.

> I see that you replaced my "LEFT OUTER JOIN" by short "JOIN" - I tested that, but it doesn't change (in any noticeable way at least) the behavior of the database.. I still have to
> wait several minutes, to end up with a "out of memory" message.
>
> If I perform such request:
> SELECT subnet.id, probes.value
>     FROM subnet
>     LEFT OUTER JOIN probes ON probes.id=subnet.probe1;
>
> I get the result after less than 1 second. So doing it on 2 columns should (I guess) still be well under 2s... I don't understand why adding a second column is so much different from
> doing the request two times?
>
> best regards,
> Mateusz

Did you look at the execution plan?  What does it look like?

Kind regards

	robert

[toc] | [prev] | [next] | [standalone]

#336

On Thu, 05 Apr 2012 23:16:27 +0200, Robert Klemme wrote:

> Did you look at the execution plan?  What does it look like?
> 
> Kind regards
> 
> 	robert

Robert, I don't understand? This looks like a syntax question, why is the 
plan important? Did I misunderstand anything here?



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#337

On 2012-04-06, Mladen Gogala <gogala.mladen@gmail.com> wrote:
> On Thu, 05 Apr 2012 23:16:27 +0200, Robert Klemme wrote:
>
>> Did you look at the execution plan?  What does it look like?
>> 
>> Kind regards
>> 
>> 	robert
>
> Robert, I don't understand? This looks like a syntax question, why is the 
> plan important? Did I misunderstand anything here?

the syntax is correct.  the plan will (for those that can read
plans) contain a lot of information not included in the original 
post such as the number and diversity of the records in the tables 
involved. also getting a plan proves the syntax was accepted.

I suspect the query is missing some constraint.

-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#338

On Fri, 06 Apr 2012 04:12:03 +0000, Jasen Betts wrote:

> the syntax is correct.

I don't think it is the correct syntax for join on two columns. I have 
shown an example of join on two columns. Syntax maybe correct, but I am 
not so sure about the logical equivalence.



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#343

On 06.04.2012 06:44, Mladen Gogala wrote:
> On Fri, 06 Apr 2012 04:12:03 +0000, Jasen Betts wrote:
>
>> the syntax is correct.
>
> I don't think it is the correct syntax for join on two columns. I have
> shown an example of join on two columns. Syntax maybe correct, but I am
> not so sure about the logical equivalence.

I hinted at the badly chosen subject above.  OP apparently really wants 
two joins.  And now it seems the problem has shifted towards long 
execution time.

Kind regards

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[toc] | [prev] | [next] | [standalone]

#349

On Fri, 06 Apr 2012 14:17:48 +0200, Robert Klemme wrote:


> I hinted at the badly chosen subject above.  OP apparently really wants
> two joins.  And now it seems the problem has shifted towards long
> execution time.

Well, controlling the execution plan is not one of the strong points of 
PostgreSQL. This is as far as I am willing to go in this discussion.



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#334

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

-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#342

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;


/Lennart

[toc] | [prev] | [next] | [standalone]

#347

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, without a
foreign key constraint (which wasn't established at the time) they could
still be different results.

-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#348

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

[toc] | [prev] | [next] | [standalone]

#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

[toc] | [prev] | [next] | [standalone]

#332

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

[toc] | [prev] | [next] | [standalone]

#341

Hi Mladen,

This is neat, thanks! I don't think it answers to my problem because in fact I don't have two distinct pairs, but two pairs sharing one common column (so two pairs formed from three elements). But nonetheless I 
wasn't aware of the possibility of using double pairs on JOINs. This will definitely be useful to me in other cases :)
I answered to my initial post, providing explanations of what exactly my problem (and need) are. I'd be happy if you could spare some minutes taking a look at it. I'm sorry if my initial post wasn't clear enough 
- I should be more specific from the beginning.

thank you!
Mateusz





On Thursday 05 April 2012 21:48, Mladen Gogala wrote:
> 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=# 
> 
> 
> 
> 

[toc] | [prev] | [next] | [standalone]

#335

On 2012-04-05, Mateusz <no@spam.please> 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?

for each row of the subnet table you're asking for the cross product
of all the probe1 values with all the probe2 values

I can't really say any more without sample data.

-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#340

On Thursday 05 April 2012 23:28, Jasen Betts wrote:
> for each row of the subnet table you're asking for the cross product
> of all the probe1 values with all the probe2 values

Hello Jasen,

Thank you for your answer. This is interesting - you are saying that instead of doing two simple JOINs on my primary table (subnet), I am in fact performing a JOIN and then a JOIN of the JOIN (which, as far as I 
understand, would increase the workload exponentially). If I get this right, it looks like the cause of my problem... Now, how do I tell it to 'just' perform two simple JOINs in one request?
I answered to my initial post, with complete explanations - would you mind taking a look?

thank you
Mateusz

[toc] | [prev] | [next] | [standalone]

#346

On 2012-04-06, Mateusz <no@spam.please> wrote:
> On Thursday 05 April 2012 23:28, Jasen Betts wrote:
>> for each row of the subnet table you're asking for the cross product
>> of all the probe1 values with all the probe2 values
>
> Hello Jasen,
>
> Thank you for your answer. This is interesting - you are saying that instead of doing two simple JOINs on my primary table (subnet), I am in fact performing a JOIN and then a JOIN of the JOIN (which, as far as I 
> understand, would increase the workload exponentially). If I get this right, it looks like the cause of my problem... Now, how do I tell it to 'just' perform two simple JOINs in one request?
> I answered to my initial post, with complete explanations - would you mind taking a look?
>

your example has value.id as a primary key, that means it's got an
implicit unique constraint on it, and my conjecture does not hold.


-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#339

Hi all,

I'd like to thank everyone for your answers. I believe I wasn't concise enough in my first post, which made you guys going in different directions (and I think none of them is the one 
I am looking for) :)
So to avoid wasting your time any more, I prepared a clear (I hope now) explanation of my problem, with real SQL requests, the example schema, etc.

First I create my tables:

CREATE TABLE probes (id INTEGER PRIMARY KEY,
                     value INTEGER NOT NULL);

CREATE TABLE subnet (id CIDR PRIMARY KEY,
                     probe1 INTEGER REFERENCES probes(id),
                     probe2 INTEGER REFERENCES probes(id));

Then, I populate tables with some fake data:

INSERT INTO probes (id, value) VALUES (1, 11);
INSERT INTO probes (id, value) VALUES (2, 12);
INSERT INTO probes (id, value) VALUES (3, 13);
INSERT INTO probes (id, value) VALUES (4, 14);
INSERT INTO probes (id, value) VALUES (5, 15);
INSERT INTO probes (id, value) VALUES (6, 16);
INSERT INTO probes (id, value) VALUES (7, 17);
INSERT INTO probes (id, value) VALUES (8, 18);
INSERT INTO subnet (id, probe1, probe2) VALUES ('1.1.1.0/24', 1, 2);
INSERT INTO subnet (id, probe1, probe2) VALUES ('1.1.2.0/24', 3, 4);
INSERT INTO subnet (id, probe1, probe2) VALUES ('1.1.3.0/24', 5, 6);
INSERT INTO subnet (id, probe1, probe2) VALUES ('1.1.4.0/24', 7, 8);

Now, what I'd need is to perform a request that would provide me with the corresponding 'probes' to every 'subnet'.

This request does a perfect job, seemingly:

SELECT subnet.id,val1.value,val2.value
  FROM subnet
  LEFT OUTER JOIN probes AS val1 ON (subnet.probe1=val1.id)
  LEFT OUTER JOIN probes AS val2 ON (subnet.probe2=val2.id);
     id     | value | value 
------------+-------+-------
 1.1.1.0/24 |    11 |    12
 1.1.2.0/24 |    13 |    14
 1.1.3.0/24 |    15 |    16
 1.1.4.0/24 |    17 |    18
(4 rows)

It works like a charm on my test-purpose database that I created for the need of this post - that is, when there are 4 entries in subnets. When I test this on my 'real' table (with 
over 300K subnets), it hangs for several minutes, and exits with an 'out of memory' error message.
However, doing the two following requests provides results immediately:

SELECT subnet.id,val1.value
  FROM subnet
  LEFT OUTER JOIN probes AS val1 ON (subnet.probe1=val1.id);

SELECT subnet.id,val2.value
  FROM subnet
  LEFT OUTER JOIN probes AS val2 ON (subnet.probe2=val2.id);

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...

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?

Best regards,
Mateusz





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

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.databases.postgresql

csiph-web