Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 1476B4ADAAC
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2011 13:22:57 -0400 (AST)
Received: from marvin.klingler.net ([91.212.121.244])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <richard@klingler.net>) id 1RZQdT-0005f9-99
	for pgsql-sql@postgresql.org; Sat, 10 Dec 2011 17:22:57 +0000
Received: from [10.0.60.1] (vpn.klingler.net [213.193.121.98])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by marvin.klingler.net (Postfix) with ESMTPSA id 3E24B33C78
	for <pgsql-sql@postgresql.org>; Sat, 10 Dec 2011 18:22:42 +0100 (CET)
Date: Sat, 10 Dec 2011 18:22:42 +0100
From: Richard Klingler <richard@klingler.net>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Message-ID: <20111210182242043383.dc56d2c5@klingler.net>
In-Reply-To: <2BBB44CA-7EFA-4084-906F-6CB72AD28C6E@yahoo.com>
References: <20111210170332479879.3df30525@klingler.net>
	<2BBB44CA-7EFA-4084-906F-6CB72AD28C6E@yahoo.com>
Subject: Re: conditional FROM
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: GyazMail version 1.5.11
X-Pg-Spam-Score: -3.8 (---)
X-Archive-Number: 201112/40
X-Sequence-Number: 36101

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.