|
James, You can reproduce that error by writing ... SELECT ... FROM a, b INNER JOIN c ON a.x=c.y The error goes away if you instead write ... SELECT ... FROM b, a INNER JOIN c ON A.x=c.y so you might try swapping FROM items i , nams.netids nPB ----- [EMAIL PROTECTED] wrote: James Black <[EMAIL PROTECTED]> wrote on 10/12/2005 09:57:51 AM:-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Due to the complexity of my query I don't know how to get this down to a simple test case to demonstrate the error. This works under mysql 4.1.8 btw, so it is failing due to a change introduced recently. It also worked under mysql 5.0.9, but I haven't tested any of the beta versions between 9 and 13. Any suggestions as to what I may look at to make this simpler? I will be working on it in the meantime, to see what I find. mysql> INSERT INTO curuse(rid, start, badge, ip, card_type, fullname, dept, college, campus) SELECT 12612,1129124442, n.badge, 0, (SELECT role FROM nams.names WHERE badge=n.badge), (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.netid='jblack') AS fullnameTable, (SELECT d.dabbr FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.college FROM nams.affiliations f, nams.roles r, nams.colleges d WHERE f.role=r.code AND f.college=d.code AND f.badge=n.badge AND d.code !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.campus FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1) 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 curuse AS c ON (c.rid=i.rid) WHERE r.lmsvalid='Y' AND a.enddate IS NULL AND i.rid=1999 AND n.netid='jblack' AND (i.status='A' OR c.badge IS NULL) LIMIT 1; ERROR 1054 (42S22): Unknown column 'i.rid' in 'on clause' +----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+----------------+ | rid | int(11) | NO | PRI | NULL | auto_increment | | lid | int(11) | NO | MUL | 0 | | | itemtype | char(4) | NO | MUL | | | | label | char(12) | NO | UNI | | | | status | char(1) | NO | | | | | layoutx | int(11) | YES | | NULL | | | layouty | int(11) | YES | | NULL | | | theta | int(11) | YES | | NULL | | +----------+----------+------+-----+---------+----------------+ 8 rows in set (0.02 sec) mysql> describe curuse; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | rid | int(11) | NO | PRI | 0 | | | start | int(11) | NO | | 0 | | | badge | int(11) | NO | MUL | 0 | | | card_type | char(2) | NO | | | | | dept | char(3) | NO | | | | | college | char(2) | NO | | | | | campus | char(1) | NO | || | | fullname | varchar(24) | NO | | | | | ip | varchar(40) | NO | PRI | || | alive | int(11) | NO | | 0 | | +-----------+-------------+------+-----+---------+-------+ 10 rows in set (0.02 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 iD8DBQFDTRZfikQgpVn8xrARAo5LAJ0fkAmKtxtMHHGWMY2kcnSOuXVCqQCbB4sH A4h+LzTgcZns66WLG2xOp9c= =YIgR -----END PGP SIGNATURE----- |
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
