Re: conditional FROM - Mailing list pgsql-sql

From Bèrto ëd Sèra
Subject Re: conditional FROM
Date
Msg-id CAKwGa_9kSD67K48mTB_kzWTo7e=3wDwU5EBLsFSVt1Ha7L09tw@mail.gmail.com
Whole thread Raw
In response to conditional FROM  (Richard Klingler <richard@klingler.net>)
List pgsql-sql
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@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


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

pgsql-sql by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: conditional FROM
Next
From: Mario Splivalo
Date:
Subject: Subselects to Joins? Or: how to design phone calls database