Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!usc!zaphod.mps.ohio-state.edu!uakari.primate.wisc.edu!aplcen!uunet!fernwood!oracle!news
From: kbittner@oracle.uucp (Kurt Bittner)
Newsgroups: comp.databases
Subject: Re: SQL, using IN with multi-column keys
Message-ID: <1990Aug21.170141.9285@oracle.com>
Date: 21 Aug 90 17:01:41 GMT
References: <6410@milton.u.washington.edu>
Reply-To: kbittner@oracle.UUCP (Kurt Bittner)
Distribution: usa
Organization: Oracle Corporation, Belmont, CA
Lines: 31

In article <6410@milton.u.washington.edu> ejbell@milton.u.washington.edu (Eric Bell) writes:
>SQL allows you to easily determine if a single value is in a list.  For
>example:
>
>	select * 
>	from data
>	where data.cnum in (select cnun from list);
>
>This is great if cnum is the key for the 'data' table.  But what do you
>do if the key is comprised of multiple columns?  Can you do something like:
>
>	select *
>	from data
>	where {data.cnum,data.cic} in (select cnum,cic from list);
>

Try using concatenation operators, as in:

 	select *
 	from data
 	where data.cnum || data.cic in (select cnum || cic from list);

Of course this requires that data.cnum, list.cnum, data.cic, and list.cic are
CHAR (can't catenate NUMBERs).  There may, of course, be more elegant solutions.

Kurt Bittner
Oracle Midwest Region
Chicago
(312) 726-1167

* My opinions are strictly my own *