[comp.databases] SQL, using IN with multi-column keys

ejbell@milton.u.washington.edu (Eric Bell) (08/17/90)

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);

Or do you have to do a join?  It seems like this would come up over and over
again, and I'm surprised that SQL doesn't support looking up a tuple in a list.

Or am I wrong?

Eric Julian Bell
Business Administration Computer Services, DJ-10
University of Washington
Seattle, WA  98195

(206)543-7125
ejbell@byron.u.washington.edu

kbittner@oracle.uucp (Kurt Bittner) (08/22/90)

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 *