JOIN where you want null columns

From: Steve Manes <smanes(at)magpie(dot)com>
To:
Cc: pgsql-general(at)postgresql(dot)org
Subject: JOIN where you want null columns
Date: 2004-02-12 19:04:20
Message-ID: 402BCE34.4080305@magpie.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm flummoxed on this one. I have a class that's building a query which
selects data from 1-n tables based on a common indexed id, io_id. These
tables may contain 1-n rows of data keyed on io_id. What I want the
query to do is return nulls for replicated columns rather than just
replicating them.

Here's the (relevant) data:

opt_io_vegetables_id:
id | io_id | opt_val
----+-------+---------
27 | 274 | 1
28 | 274 | 3
29 | 274 | 5
30 | 274 | 7

opt_io_fruits_id:

id | io_id | opt_val
----+-------+---------
12 | 274 | 9

opt_io_name_text:

id | io_id | opt_val
----+-------+---------------------------------
12 | 274 | Text... text... text... text...

I have this query:

SELECT
A.opt_val,
B.opt_val,
C.opt_val
FROM
IO io
INNER JOIN opt_io_vegetables_id A ON io.id = A.io_id
INNER JOIN opt_io_fruits_id B ON io.id = B.io_id
INNER JOIN opt_io_name_text C ON io.id = C.io_id
WHERE
io.id = 274;

It returns:

opt_val | opt_val | opt_val
---------+---------+---------------------------------
1 | 9 | Text... text... text... text...
3 | 9 | Text... text... text... text...
5 | 9 | Text... text... text... text...
7 | 9 | Text... text... text... text...

What I'd *like* the query to do for the replicated columns in $col[1]
and $col[2] is return nulls.

Is there any way to do this?

In response to

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2004-02-12 19:20:07 Re: help with query speed
Previous Message Dann Corbit 2004-02-12 18:49:45 Re: help with query speed