List:General Discussion« Previous MessageNext Message »
From:Jeff Shapiro Date:April 2 2003 10:28pm
Subject:RE: select help
View as plain text  
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
>


Thread
select helpJohn Hoskins2 Apr
  • RE: select helpMichael Shulman2 Apr
    • RE: select helpJohn Hoskins2 Apr
      • RE: select helpMichael Shulman2 Apr
RE: select helpMichael Shulman2 Apr
  • RE: select helpJohn Hoskins2 Apr
    • RE: select helpJeff Shapiro3 Apr
      • Re: select helpBruce Feist3 Apr