Thread: conditional FROM

conditional FROM

From
Richard Klingler
Date:
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 keyport2node    index to table nodeport2card    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



Re: conditional FROM

From
Andreas Kretschmer
Date:
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°


Re: conditional FROM

From
David Johnston
Date:
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.


Re: conditional FROM

From
Richard Klingler
Date:
This seems to do the trick...

select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan 
from arp, port, node
wherearp.arp2port = port.portid and port.name = 'Fa1/0/1'and port.port2node = node.nodeidand node.name like
'nodename%'
union
select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan 
from arp, port, card, node
wherearp.arp2port = port.portid and port.name = 'Fa1/0/1'and port.port2card = card.cardidand card.card2node =
node.nodeidandnode.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.


Re: conditional FROM

From
Andreas Kretschmer
Date:
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°


Re: conditional FROM

From
Bèrto ëd Sèra
Date:
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.

Re: conditional FROM

From
Jasen Betts
Date:
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