replies embedded....
James Black <[EMAIL PROTECTED]> wrote on 07/13/2005 10:03:09 AM:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> If I run the first query on mysql 5.0.6 the results are correct, I get
> 142 rows, if I run it on 5.0.7 I get 8 rows back.
>
> If I remove the nested select, as shown in the second query, then it
> works fine on 5.0.7.
>
> I am trying to understand what is going on. I may try to create a test
> case to demonstrate this, but I don't understand why the problem is
> happening in the first place.
>
> This is on Solaris 8 and Solaris 9, btw.
>
> Thank you for any suggestions.
>
(Your original query, reformatted to eliminate most of the wrapping)
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;
(Your second query, also reformatted to minimize wrapping)
SELECT i.label
, i.itemtype
, it.name
, i.rid
, i.status
, l.locname
, i.lid
,(SELECT IF(p.fullname IS NULL
, 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;
> - --
> "Love is mutual self-giving that ends in self-recovery." Fulton Sheen
> James Black [EMAIL PROTECTED]
Maybe I missed something but the second query is INCOMPLETE! You are
missing a Parenthesis to close the inner select you are using to populate
your second query's eighth term and there is no table information (no FROM
clause) for your outer select (your main query clause). I can't try to
help that one when I don't have all of the information to work with.
Your first query I would categorize as "excessive abuse of subqueries". I
would reformat it like this:
SELECT i.label
, i.itemtype
, it.name
, i.rid
, i.status
, l.locname
, i.lid
, IF(p.fullname IS NULL
, CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ',
sna.gen)
, p.fullname
) 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 JOIN curuse AS c
ON c.rid=i.rid
LEFT JOIN nams.prefs AS p
ON p.badge = c.badge
LEFT JOIN nams.names AS sna
ON sna.badge=c.badge
WHERE l.locname='iclab'
ORDER BY i.status, i.label;
That should provide the same results on both versions (assuming identical
data).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
