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

From: Oeln <ohmy9od_at_yahoo.com>
Date: 8 Jan 2004 19:48:38 -0800
Message-ID: <ffde43bc.0401081948.71e6cc0e@posting.google.com>

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?) Received on Thu Jan 08 2004 - 21:48:38 CST