[GENERAL] JOIN where you want null columns

[Steve Manes]

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?



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match