Re: conditional FROM

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.