kmh496 wrote:
hi,
i am running a join query between the german_english table and the
user_todo_german_english tables, to figure out which words a user is
studying.
the key i am using is a combination of wordid + pos + posn (that is part
of speech == pos)
however, i am not able to correctly do the join because of improper
results. please see the following
mysql> select * from user_todo_german_english where date_col >
'2006-04-22 07:00:00';
+---------+--------+------+------+---------------------+
| mb_id | wordid | posn | pos | date_col |
+---------+--------+------+------+---------------------+
| curious | 94122 | 1 | 0 | 2006-04-22 07:04:26 |
| curious | 327400 | 1 | 0 | 2006-04-22 07:04:40 |
| curious | 327402 | 1 | 0 | 2006-04-22 07:40:41 |
| curious | 59553 | 1 | 0 | 2006-04-22 07:40:55 |
| curious | 86851 | 1 | 0 | 2006-04-22 08:14:12 |
+---------+--------+------+------+---------------------+
5 rows in set (0.00 sec)
mysql> select wordid,pos,posn from german_english where wordid in
(86851,59553);
+--------+------+------+
| wordid | pos | posn |
+--------+------+------+
| 59553 | m | 1 |
| 86851 | m | 1 |
+--------+------+------+
2 rows in set (0.00 sec)
mysql> select * from user_todo_german_english where wordid = '86851' and
posn = '1' and pos = 'm' AND mb_id='curious';
+---------+--------+------+------+---------------------+
| mb_id | wordid | posn | pos | date_col |
+---------+--------+------+------+---------------------+
| curious | 86851 | 1 | 0 | 2006-04-22 08:14:12 |
+---------+--------+------+------+---------------------+
1 row in set, 1 warning (0.00 sec)
clearly, pos is ZERO in the database, and the letter 'm' in the query.
or is my brain farting again?
You didn't provide the table definitions (output of SHOW CREATE TABLE works
well), but it appears that pos is a numeric column in user_todo_german_english,
but a string column in german_english.
I notice that every constant in your WHERE clauses is quoted. You need to
realize that 86851 is a number, but '86851' is a string. When mysql sees
WHERE numeric_col = 'some string'
it must convert 'some string' to a numeric value in order to make the
comparison. That is merely an invisible time waster when 'some string' is just
a quoted number. In the case of
pos = 'm'
however, you get correct, but unanticipated behavior. As pos is a numeric
column, 'm' must be converted to a number for the comparison. Because m is not
a valid numeric value, it gets converted to 0. Hence the result you see.
You can find the numeric value of any string by adding 0 to it. For example,
mysql> SELECT '86851', '86851' + 0, 'm', 'm' + 0;
+-------+-------------+---+---------+
| 86851 | '86851' + 0 | m | 'm' + 0 |
+-------+-------------+---+---------+
| 86851 | 86851 | m | 0 |
+-------+-------------+---+---------+
1 row in set (0.00 sec)
You should do two things:
1) Stop quoting numbers.
2) If 'm' is a valid value for pos, then pos should be a string column rather
than numeric.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
