From: Richard Klingler <richard@klingler.net>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: conditional FROM
Date: Sat, 10 Dec 2011 18:22:42 +0100
Message-ID: <20111210182242043383.dc56d2c5@klingler.net> (raw)
In-Reply-To: <2BBB44CA-7EFA-4084-906F-6CB72AD28C6E@yahoo.com>
References: <20111210170332479879.3df30525@klingler.net>
<2BBB44CA-7EFA-4084-906F-6CB72AD28C6E@yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@klingler.net
Subject: Re: conditional FROM
In-Reply-To: <20111210182242043383.dc56d2c5@klingler.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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