Re: conditional FROM - Mailing list pgsql-sql

From Richard Klingler
Subject Re: conditional FROM
Date
Msg-id 20111210182242043383.dc56d2c5@klingler.net
Whole thread Raw
In response to Re: conditional FROM  (David Johnston <polobo@yahoo.com>)
Responses Re: conditional FROM
List 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
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.


pgsql-sql by date:

Previous
From: David Johnston
Date:
Subject: Re: conditional FROM
Next
From: Andreas Kretschmer
Date:
Subject: Re: conditional FROM