If you want to be a bit more generic you could do something like this:
# store the desired OS ID into a variable
SELECT @desired_id := os_id FROM os_table WHERE os_name = "win nt";
# now find the solutions that match with the os_id
SELECT o.os_id, o.os_name, s.os_code, s.solution
FROM os_table o, solution_table s
WHERE (o.os_id & s.os_code) = @desired_id;
+-------+---------+---------+---------------------+
| os_id | os_name | os_code | solution |
+-------+---------+---------+---------------------+
| 8 | win nt | 24 | nt and 2000 dun fix |
| 8 | win nt | 255 | no-pay contact CSRs |
+-------+---------+---------+---------------------+
2 rows in set (0.01 sec)
--
Jeff Shapiro | Starlight Spectacular Ride
Webmaster | June 21st,2003 at midnight
www.starlightspectacular.org | Benefiting the Trails & Open Space Coalition
On 4/2/03 at 15:40, John Hoskins spoke thusly:
>This one worked. Thank you.
>
>On Wed, 2 Apr 2003, Michael Shulman wrote:
>
>> John,
>>
>> Looks like I'm first with the wrong answer again.
>>
>> This time for sure.
>>
>> How about:
>> AND os.os_id & 8 = 8
>>
>> Where 8 is the value that you're looking for.
>>
>> -ms
>>
>>
>>
>>
>> -----Original Message-----
>> From: Michael Shulman [mailto:mjs@stripped]
>> Sent: Wednesday, April 02, 2003 11:38 AM
>> To: 'John Hoskins'
>> Cc: 'mysql@stripped'
>> Subject: RE: select help
>>
>> No problem. Use mod(m,n). To get the records where the "8" bit is set, use
>> and mod(os.os_id,8) = 0;
>>
>> mysql> use test
>> Database changed
>> mysql> create table t (i integer);
>> Query OK, 0 rows affected (0.18 sec)
>>
>> mysql> insert into t values (1);
>> Query OK, 1 row affected (0.10 sec)
>>
>> mysql> insert into t values (2);
>> Query OK, 1 row affected (0.00 sec)
>>
>> <rows omitted for brevity, values 3..7 inserted>
>>
>> mysql> insert into t values (8);
>> Query OK, 1 row affected (0.00 sec)
>>
>> mysql> select * from t where mod(i,2) = 0;
>> +------+
>> | i |
>> +------+
>> | 2 |
>> | 4 |
>> | 6 |
>> | 8 |
>> +------+
>> 4 rows in set (0.00 sec)
>>
>> mysql> select * from t where mod(i,4) = 0;
>> +------+
>> | i |
>> +------+
>> | 4 |
>> | 8 |
>> +------+
>> 2 rows in set (0.00 sec)
>>
>> -----Original Message-----
>> From: John Hoskins [mailto:jhoskins@stripped]
>> Sent: Wednesday, April 02, 2003 11:34 AM
>> To: Michael Shulman
>> Cc: mysql@stripped
>> Subject: RE: select help
>>
>> Not quite that simple, Plese read the last of the original post. I need
>> all solutions that have the 4th bit on, so 8,15,24,31...255 all have the
>> 4th bit in combination with other bits.
>>
>> On Wed, 2 Apr 2003, Michael Shulman wrote:
>>
>> > mysql> select solution
>> > -> from os_table os, solutions_table solutions
>> > -> where os.os_id = solutions.os_code
>> > -> and os.os_id = 8;
>> >
>> > -ms
>> >
>> >
>> > -----Original Message-----
>> > From: John Hoskins [mailto:jhoskins@stripped]
>> > Sent: Wednesday, April 02, 2003 10:41 AM
>> > To: mysql@stripped
>> > Subject: select help
>> >
>> >
>> > Please consider the following two tables:
>> >
>> > mysql> select * from os_table;
>> > +-------+----------+
>> > | os_id | os_name |
>> > +-------+----------+
>> > | 1 | mac os |
>> > | 2 | win 95 |
>> > | 4 | win 98 |
>> > | 8 | win nt |
>> > | 16 | win 2000 |
>> > | 32 | win me |
>> > | 64 | xp home |
>> > | 128 | xp pro |
>> > +-------+----------+
>> >
>> > mysql> select * from solution_table;
>> > +---------+---------------------+
>> > | os_code | solution |
>> > +---------+---------------------+
>> > | 1 | mac fix |
>> > | 24 | nt and 2000 dun fix |
>> > | 255 | no-pay contact CSRs |
>> > +---------+---------------------+
>> >
>> >
>> > What I'd like to do is select all solutions that
>> > applys to NT, os_id.os_table=8
>> >
>> > so it should return solution 24 and 255. Since these two solutions have
>> > the NT os bit turned on.
>> >
>> > Thank You
>> > John H.
>> >
>> >
>> >
>> >
>>
>>
>>
>>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
