Re: Multiple selects in one

From: joel garry <>
Date: Wed, 10 Mar 2010 14:30:23 -0800 (PST)
Message-ID: <>

On Mar 10, 1:49 pm, The Magnet <> wrote:
> I'm trying to write a query that will combine multiple rows from the
> same table into one row.  That can be done with each column being a
> SELECT statement.
> However, in my case each column has different criteria and I'm running
> into a problem where if any of the criteria from any of the queries is
> false, the entire query returns nothing.
> SELECT customer_id, customer_name, new_order_id, old_order_id.........
> FROM (SELECT customer_id, customer_name, new_order_id
>             FROM......
>             WHERE........),
>           (SELECT  old_order_id
>             FROM......
>             WHERE........)
> Basically I'm applying separate criteria to each column.  How can I
> allow any of the column queries to be false and just return NULL or
> whatever instead of the entire query failing?

It would be easier to work out if you supplied some create statements/ test data, but maybe FULL OUTER JOIN as a self-join will get you there. See FOJ examples in docs. There's probably a nested table, associative array or similar PL solution, too, and maybe an analytic over a UNION ALL way.


-- is bogus.
Received on Wed Mar 10 2010 - 16:30:23 CST

Original text of this message