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