Re: SELECT in problem

From: jefftyzzer <jefftyzzer_at_sbcglobal.net>
Date: Wed, 3 Mar 2010 12:25:20 -0800 (PST)
Message-ID: <07c710d1-5277-4e8f-971f-4d5dfb7764aa_at_q21g2000yqm.googlegroups.com>

On Mar 3, 5:45 am, Pedro <pedro.p..._at_gmail.com> wrote:
> Good morning people. I need quiet an odd help in here. Let me see if I
> can explain it.
>
> I have an SELECT WHERE IN (<list of ids>).
> Let us say that I sent the following list of ids: 1, 2 and 3.
>
> There is only resulting records for the ID = 3, but I'd like to see
> all the ids, even if they don't have records...
> I expect the following result for instance:
>
> ID     DESC
> 1
> 2
> 3      Test
>
> Any suggestion on how to do this? I have here the complete statement,
> but I don't think it would help for now...

An elaboration of Carlos's sugestion:

WITH
    IDS AS
    (

        SELECT
                1 ID
        FROM
            DUAL
        UNION ALL
        SELECT
                2
        FROM
            DUAL
        UNION ALL
        SELECT
                3
        FROM
            DUAL

    ),
    T AS
    (
    SELECT
        3 ID, 'TEST' DESCR
    FROM
        DUAL
    )
SELECT
    I.ID,
    T.DESCR
FROM
    IDS I
LEFT JOIN
    T
ON

    I.ID = T.ID
ORDER BY
    1;

--Jeff Received on Wed Mar 03 2010 - 14:25:20 CST