Re: Identifying ID in one field by conditions in other fields..

Oeln wrote:

> I've got the following table:
>
> Opt
> __________
> Opt_ID | OID | OptInfo
> __________
> 100 | 1 | 1000
> 101 | 1 | 1000
> 101 | 2 | 900
> 101 | 3 | 800
> 102 | 1 | 1100
> 103 | 1 | 900
> 103 | 2 | 800
>
> Items with an Opt_ID of 101 include OID of 1, 2, 3 and Optinfo of
> 1000, 900, 800. The item with an Opt_ID of 100 includes one with an
> OID of 1 and OptInfo of 1000 (identical to one of the items with an
> Opt_ID of 101). If I input
>
> select opt_id from opt where (oid = 1 and optinfo = 1000);
>
> I get Opt_ID's 100 and 101. I'd like to isolate, and find, only the
> Opt_ID which includes 1000, 900 and 800, in that order as indicated by
> the incrementing OID. In other words:
>
> select opt_id from opt where (oid = 1 and optinfo = 1000)
> and (oid = 2 and optinfo = 900) and (oid = 3 and optinfo = 800);
>
> Of course, inputting this instead I get no items, because no one
> individual occurence of Opt_ID 101 includes each of the OptInfo
> indicated (or each instance of Opt_ID 101 only includes one of them).
> Obviously, if I insert 'or' instead of 'and' I get Opt_ID 100 and 101,
> because of the first condition. I'd imagine there's got to be a way to
> isolate Opt_ID 101 from the others by identifying it as the only one
> to include each of the conditions..
>
> (I imagine 'count' ought to be involved in this?)

And I imagine this is homework from a class so you should expect hints rather than complete answers. With that in mind ... try using the OR operator in addition to AND in your WHERE clause. Also consider how you might use IN.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)