Don, It is a little hard for me to comment on the query because I don't have the context I need. When I tackle problems like this I ignore the query and ask "what are you trying to DO?" The queries you sent look like tunnel vision to me; there is probably a better way to do what you're trying to do, which may not resemble the queries you posted at all.
That said, I see a number of issues with the queries you posted. Although they are legal SQL, they are buggy. You can write bugs in SQL just like any language. For example, you are using LEFT JOIN apparently without understanding it fully. Take a look at this excerpt: > FROM tl_appt apt > LEFT JOIN tl_rooms r on r.room_id = apt.room_id > WHERE > r.location_id = '1' You are converting the LEFT JOIN (which is a synonym for LEFT OUTER JOIN) into an INNER JOIN with the constraint in the WHERE clause. LEFT JOIN is designed to include rows in the left-hand table that have no matching rows in the right-hand table (r, in this case), and will fill the missing "cells" in the resultset with NULL. But r.location_id will filter out such results. In general, use LEFT JOIN only when you need it. Otherwise just use JOIN (which is a synonym for INNER JOIN). Other problems I see: - You are joining to the same tables in multiple places. It looks to me like your query needs refactoring, at the least. I'm not using refactoring in the Java sense here, but in the sense of algebra. For example, you know that AB+AC is the same as A(B+C). The redundant mentions of some of those tables seem to need a similar refactoring to me: pull out the common "terms" (tables) and access them only once. - Your nested subqueries seem to be an overly complex, and possibly wrong, way to approach the problem. That's just my gut feeling based on "code smell." In general, a subquery in the FROM clause (in your case, aliased as q1) is only needed when GROUP BY is required, and using such a subquery for the sole purpose of wrapping a "WHERE q1.previous = 0" around it smells like something needs to be unwrapped. The WHERE clause could be pushed into the subquery, and the subquery thus removed. - Your q1 subquery has non-deterministic behavior because you're selecting non-grouped columns. You're selecting last and first names, for example, but those are neither constants nor grouped-by, and thus are not constant per-group. You are going to get a pseudo-random and nondeterministic value for each group. This alone could account for the problems you're seeing. To avoid this problem you can try running your query with ONLY_FULL_GROUP_BY in the SQL_MODE variable. That setting will throw an error rather than silently running the query and returning random values from the group. - I usually find that subqueries embedded "inline" into the column list (in your case, the subqueries for "previous" and "dr_all_ther_qty") are better replaced by something else. Not for performance reasons -- but because the set-based thinking becomes a mixture of set-based and FOREACH-like. You know, "for each row I find, execute this subquery and generate a number of previous XYZ..." My experience has been that this quickly confuses query authors and makes them write something that's not what they intend. I can't look at the query and say it's not what you intend, because I don't know your intention, but again I'm reacting to "code smell" and gut feeling. Those are just some observations that may be helpful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
