-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Using mysql 5.0.15 my query gets a result of "Unknown column 'i.rid' in
'on clause'"
There is actually a complete query, but this is a snippet, as the
selects with subqueries I don't believe will cause the problem. When I
changed c.rid=i.rid to c.rid=c.rid, the query executes. It is incorrect,
but at least it runs, so, it appears that that the last join is where
the problem is.
Any suggestions as to what might be the cause? Thanx.
FROM items i, nams.netids n
INNER JOIN nams.names AS na ON n.badge=na.badge
INNER JOIN nams.affiliations AS a ON a.badge=na.badge
INNER JOIN nams.roles AS r ON a.role=r.code
LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge)
LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid)
WHERE i.rid=12415 AND n.netid='alb' AND i.status='A' AND c.badge IS NULL
LIMIT 1;
When I use explain on mysql 4.1.08 I get this:
- ----------------------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+--------------------+-------+--------+--------------------------------+------------+---------+----------------+------+----------------------------------------------+
| 1 | PRIMARY | i | const | PRIMARY
| PRIMARY | 4 | const | 1 |
|
| 1 | PRIMARY | n | const | PRIMARY,netid_badge_ndx
| PRIMARY | 12 | const | 1 |
|
| 1 | PRIMARY | na | const | PRIMARY
| PRIMARY | 4 | const | 1 | Using index
|
| 1 | PRIMARY | a | ref | badge_ndx,role_ndx
| badge_ndx | 4 | const | 1 |
|
| 1 | PRIMARY | r | eq_ref | PRIMARY
| PRIMARY | 2 | nams.a.role | 1 |
|
| 1 | PRIMARY | t | ref | badge_ndx
| badge_ndx | 4 | const | 12 | Using where
|
| 1 | PRIMARY | c | ref | curuse_ndx
| curuse_ndx | 4 | const deptid | 1 | Using where
|
| 6 | DEPENDENT SUBQUERY | f | ref |
badge_ndx,role_ndx,college_ndx | badge_ndx | 4 | const |
1 | Using where; Using temporary; Using filesort |
| 6 | DEPENDENT SUBQUERY | r | eq_ref | PRIMARY
| PRIMARY | 2 | nams.f.role | 1 |
|
| 6 | DEPENDENT SUBQUERY | d | eq_ref | PRIMARY
| PRIMARY | 2 | nams.f.college | 1 | Using where; Using
index |
| 5 | DEPENDENT SUBQUERY | f | ref | badge_ndx,role_ndx,dept_ndx
| badge_ndx | 4 | const | 1 | Using where; Using
temporary; Using filesort |
| 5 | DEPENDENT SUBQUERY | r | eq_ref | PRIMARY
| PRIMARY | 2 | nams.f.role | 1 |
|
| 5 | DEPENDENT SUBQUERY | d | eq_ref | PRIMARY,dabbr_ndx
| PRIMARY | 4 | 4 | const | 1 |
|
| 2 | DEPENDENT SUBQUERY | names | const | PRIMARY
| PRIMARY | 4 | const | 1 |
|
+----+--------------------+-------+--------+--------------------------------+------------+---------+----------------+------+----------------------------------------------+
20 rows in set (0.01 sec)
- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black [EMAIL PROTECTED]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFDgdwkikQgpVn8xrARAky9AJ4lcBoVIsqkU2nBpca6fAZZsl5dvgCfR3H5
7ed5A7RcOLrcm2XxQh/zSD8=
=+Xos
-----END PGP SIGNATURE-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
