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 n 
PB

-----

SGreen@unimin.com wrote:
James Black <jblack@ieee.org> 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    james@usf.edu
-----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-----

    

Just to help the rest of us, here is his query reformatted only (no 
changes except for spacing and tabbing). Some of the longer CONCAT() 
functions will wrap but the majority of it should be much easier to read:

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;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
  

No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005