Re: conditional FROM

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

Richard Klingler <richard(at)klingler(dot)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°

On Dec 10, 2011, at 11:03, Richard Klingler <richard(at)klingler(dot)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.

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(at)klingler(dot)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.

Richard Klingler <richard(at)klingler(dot)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°

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(at)klingler(dot)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(at)postgresql(dot)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.

On 2011-12-10, Richard Klingler <richard(at)klingler(dot)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