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@stripped wrote:
>James Black <jblack@stripped> 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@stripped
>>-----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
>
>
>
Attachment: [text/html]
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
