Re: conditional FROM

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(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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)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.