pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconditional FROM
7+ messages / 5 participants
[nested] [flat]
* conditional FROM
@ 2011-12-10 16:03 Richard Klingler <richard@klingler.net>
0 siblings, 4 replies; 7+ messages in thread
From: Richard Klingler @ 2011-12-10 16:03 UTC (permalink / raw)
To: pgsql-sql
Good day...
I'm trying to build a query for PGSQL 9.1 where a table has two
references with only one being used depending of the type of entry..
For example, the table has following simplified structure:
portid primary key
port2node index to table node
port2card index to table card
So how can I do a conditional FROM clause in the query depending on the
column port2node and port2card?
If port2card is Null or 0 I don't want it in the FROM clause as the
query will return unneccessary duplicate
row...the same goes for port2node being Null or 0...
thanx in advance
richard
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: conditional FROM
@ 2011-12-10 16:17 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Richard Klingler <richard@klingler.net>
3 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2011-12-10 16:17 UTC (permalink / raw)
To: pgsql-sql
Richard Klingler <richard@klingler.net> wrote:
> Good day...
>
> I'm trying to build a query for PGSQL 9.1 where a table has two
> references with only one being used depending of the type of entry..
>
> For example, the table has following simplified structure:
>
> portid primary key
> port2node index to table node
> port2card index to table card
>
> So how can I do a conditional FROM clause in the query depending on the
> column port2node and port2card?
> If port2card is Null or 0 I don't want it in the FROM clause as the
> query will return unneccessary duplicate
> row...the same goes for port2node being Null or 0...
I think, you can't do that, you have to build your query and execute
that string. You should use a function to do that.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: conditional FROM
@ 2011-12-10 16:28 David Johnston <polobo@yahoo.com>
parent: Richard Klingler <richard@klingler.net>
3 siblings, 1 reply; 7+ messages in thread
From: David Johnston @ 2011-12-10 16:28 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql
On Dec 10, 2011, at 11:03, Richard Klingler <richard@klingler.net> wrote:
> Good day...
>
> I'm trying to build a query for PGSQL 9.1 where a table has two
> references with only one being used depending of the type of entry..
>
> For example, the table has following simplified structure:
>
> portid primary key
> port2node index to table node
> port2card index to table card
>
> So how can I do a conditional FROM clause in the query depending on the
> column port2node and port2card?
> If port2card is Null or 0 I don't want it in the FROM clause as the
> query will return unneccessary duplicate
> row...the same goes for port2node being Null or 0...
>
>
> thanx in advance
> richard
>
>
Two options (one of which may not work for you).
1. Write two queries, one for each table, and union the results.
2. Use LEFT JOINs (somehow...)
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: conditional FROM
@ 2011-12-10 17:22 Richard Klingler <richard@klingler.net>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Klingler @ 2011-12-10 17:22 UTC (permalink / raw)
To: pgsql-sql
This seems to do the trick...
select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan
from arp, port, node
where
arp.arp2port = port.portid and port.name = 'Fa1/0/1'
and port.port2node = node.nodeid
and node.name like 'nodename%'
union
select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan
from arp, port, card, node
where
arp.arp2port = port.portid and port.name = 'Fa1/0/1'
and port.port2card = card.cardid
and card.card2node = node.nodeid
and node.name like 'nodename%'
;
Though I just can't order the rows anymore by inet(arp.ip) anymore...
Any hints on my ordering isn't anylonger possible?
But at least the query is way faster than before (o;
2msec instead of 650msecs (o;
thanx ina dvance
richard
On Sat, 10 Dec 2011 11:28:29 -0500, David Johnston wrote:
> On Dec 10, 2011, at 11:03, Richard Klingler <richard@klingler.net> wrote:
>
>> Good day...
>>
>> I'm trying to build a query for PGSQL 9.1 where a table has two
>> references with only one being used depending of the type of entry..
>>
>> For example, the table has following simplified structure:
>>
>> portid primary key
>> port2node index to table node
>> port2card index to table card
>>
>> So how can I do a conditional FROM clause in the query depending on the
>> column port2node and port2card?
>> If port2card is Null or 0 I don't want it in the FROM clause as the
>> query will return unneccessary duplicate
>> row...the same goes for port2node being Null or 0...
>>
>>
>> thanx in advance
>> richard
>>
>>
>
> Two options (one of which may not work for you).
>
> 1. Write two queries, one for each table, and union the results.
> 2. Use LEFT JOINs (somehow...)
>
> David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: conditional FROM
@ 2011-12-10 17:30 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Richard Klingler <richard@klingler.net>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2011-12-10 17:30 UTC (permalink / raw)
To: pgsql-sql
Richard Klingler <richard@klingler.net> wrote:
> This seems to do the trick...
>
> select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan
> from arp, port, node
> where
> arp.arp2port = port.portid and port.name = 'Fa1/0/1'
> and port.port2node = node.nodeid
> and node.name like 'nodename%'
> union
> select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan
> from arp, port, card, node
> where
> arp.arp2port = port.portid and port.name = 'Fa1/0/1'
> and port.port2card = card.cardid
> and card.card2node = node.nodeid
> and node.name like 'nodename%'
> ;
>
> Though I just can't order the rows anymore by inet(arp.ip) anymore...
> Any hints on my ordering isn't anylonger possible?
select * from (insert the query above here) foo order by ...
Regards...
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: conditional FROM
@ 2011-12-10 17:39 Bèrto ëd Sèra <berto.d.sera@gmail.com>
parent: Richard Klingler <richard@klingler.net>
3 siblings, 0 replies; 7+ messages in thread
From: Bèrto ëd Sèra @ 2011-12-10 17:39 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql
I guess this is what you mean:
create table node(
id bigint primary key);
insert into node values (1);
insert into node values (2);
create table card(
id integer primary key);
insert into card values (1);
insert into card values (2);
create table port_activity (
portid integer primary key,
port2node bigint,
port2card integer );
alter table port_activity add constraint myconst1 foreign key (port2node)
references node (id);
alter table port_activity add constraint myconst2 foreign key (port2card)
references card (id);
insert into port_activity values (1,1,NULL);
insert into port_activity values (2,NULL,1);
select
p.portid as port,
coalesce(n.id,c.id) as destination
from
port_activity as p
left outer join node as n
on p.port2node=n.id
left outer join card as c
on p.port2card=c.id;
Best regards
Bèrto
On 10 December 2011 19:03, Richard Klingler <richard@klingler.net> wrote:
> Good day...
>
> I'm trying to build a query for PGSQL 9.1 where a table has two
> references with only one being used depending of the type of entry..
>
> For example, the table has following simplified structure:
>
> portid primary key
> port2node index to table node
> port2card index to table card
>
> So how can I do a conditional FROM clause in the query depending on the
> column port2node and port2card?
> If port2card is Null or 0 I don't want it in the FROM clause as the
> query will return unneccessary duplicate
> row...the same goes for port2node being Null or 0...
>
>
> thanx in advance
> richard
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: conditional FROM
@ 2011-12-10 22:32 Jasen Betts <jasen@xnet.co.nz>
parent: Richard Klingler <richard@klingler.net>
3 siblings, 0 replies; 7+ messages in thread
From: Jasen Betts @ 2011-12-10 22:32 UTC (permalink / raw)
To: pgsql-sql
On 2011-12-10, Richard Klingler <richard@klingler.net> wrote:
> Good day...
>
> I'm trying to build a query for PGSQL 9.1 where a table has two
> references with only one being used depending of the type of entry..
>
> For example, the table has following simplified structure:
>
> portid primary key
> port2node index to table node
> port2card index to table card
>
> So how can I do a conditional FROM clause in the query depending on the
> column port2node and port2card?
> If port2card is Null or 0 I don't want it in the FROM clause as the
> query will return unneccessary duplicate
> row...the same goes for port2node being Null or 0...
use left outer join.
SELECT * FROM
port
LEFT OUTER JOIN node ON node.nodeid=port.port2node
LEFT OUTER JOIN card ON card.cardid=port.port2card
or something like that.
You may find coalesce() useful to combine columns where node and card
both carry equivalent information.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-12-10 22:32 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-10 16:03 conditional FROM Richard Klingler <richard@klingler.net>
2011-12-10 16:17 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-10 16:28 ` David Johnston <polobo@yahoo.com>
2011-12-10 17:22 ` Richard Klingler <richard@klingler.net>
2011-12-10 17:30 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-10 17:39 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-10 22:32 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox