RE: SQL question: ...where x in (1,2,3)

From: Daemen, Remco <R.Daemen_at_facent.nl>
Date: Wed, 11 Oct 2000 10:04:54 +0200
Message-Id: <10646.118939@fatcity.com>

Try merging the three sub-selects into one query using UNION ALL

Example:

select count(*) from a
union all
select count(*) from d;

 COUNT(*)

---

       20
        0

select 'HIT !' from dual where 20 in (
select count(*) from a
union all
select count(*) from d );

'HIT!

---

HIT ! HTH, Remco

> ----------
> From: Helmut Daiminger[SMTP:hdaiminger_at_vivonet.com]
> Sent: woensdag 11 oktober 2000 2:00
> To: Multiple recipients of list ORACLE-L
> Subject: SQL question: ...where x in (1,2,3)
>
> Hi!
>
> I got a (hopefully) simple SQL question:
>
> select distinct * from Tb_dept
> where DeptID = 64
> and CompanyID = 12345
> and Status='AC'
> and StoreLocID in (xx,yy,zz)
>
> The values xx,yy, zz come from a sub-select statement like this:
>
> select storelocID from
> ( select count(*) as numstores from tbdept
> where companyID = 12345
> and storelocID in (1,2)
> and status = 'AC'
> and Deptid = 64
> group by storelocID
> )
>
> The question is: the number of rows returned from the sub-select may be
> different (i.e. just xx or xx and yy and zz).
>
> How can I modify the first statement that I only get rows back that are in
> all StoreLocID's ???
>
> Sounds confusing?
>
> Thanks,
> Helmut
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Helmut Daiminger
> INET: hdaiminger_at_vivonet.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Wed Oct 11 2000 - 03:04:54 CDT