Prev: The penny hasn't dropped yet...
Next: Spying on queries from application?
From: Pedro on 3 Mar 2010 08:45
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...
From: Carlos on 3 Mar 2010 09:13
On Mar 3, 2:45 pm, Pedro <pedro.p...(a)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...

OUTER JOIN with a table 'ad-hoc' for the desired values (possibly with
a 'WITH' sentence)

HTH.

Cheers.

Carlos.
From: jefftyzzer on 3 Mar 2010 15:25
On Mar 3, 5:45 am, Pedro <pedro.p...(a)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
From: Marconelli on 4 Mar 2010 08:55

Uzytkownik "jefftyzzer" <jefftyzzer(a)sbcglobal.net> napisal w wiadomosci
news:07c710d1-5277-4e8f-971f-4d5dfb7764aa(a)q21g2000yqm.googlegroups.com...
On Mar 3, 5:45 am, Pedro <pedro.p...(a)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...

Here's a little nicer version:

WITH counter AS (SELECT level id FROM dual CONNECT BY level <=3)
SELECT c.id, t.*
FROM counter c
LEFT OUTER JOIN table t ON c.id = t.id


From: jefftyzzer on 4 Mar 2010 13:50
On Mar 4, 5:55 am, "Marconelli" <m...(a)NOSPAM.go2.pl> wrote:
> Uzytkownik "jefftyzzer" <jefftyz...(a)sbcglobal.net> napisal w wiadomoscinews:07c710d1-5277-4e8f-971f-4d5dfb7764aa(a)q21g2000yqm.googlegroups.com...
> On Mar 3, 5:45 am, Pedro <pedro.p...(a)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...
>
> Here's a little nicer version:
>
> WITH counter AS (SELECT level id FROM dual CONNECT BY level <=3)
> SELECT c.id, t.*
>   FROM counter c
>   LEFT OUTER JOIN table t ON c.id = t.id

"SELECT level id FROM dual CONNECT BY level <=3"--very nice ;-)

--Jeff
 | 
Pages: 1
Prev: The penny hasn't dropped yet...
Next: Spying on queries from application?