-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I have a query that uses a subquery that works under mysql 4.1 and 5.0.6, but doesn't work on 5.0.7 or 5.0.9
I am trying to understand why it may be having a problem so I can make a test case to demonstrate it, and am hoping that someone may have an idea. SELECT i.label, i.itemtype, it.name, i.rid, i.status, l.locname, i.lid, (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.badge=c.badge) AS UserFullname, i.layoutx, i.layouty, i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; If I take out the following part then the query works fine. (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.badge=c.badge) AS UserFullname, - -- "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 iD8DBQFC26O3ikQgpVn8xrARArFEAKCPBVaU85aA4gR0DOjcoN5lNxqvuwCghLAO bGNL/jNf65hw6u8RBOFR2Eg= =N9SC -----END PGP SIGNATURE----- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]