About Dan Wilson
Dan Wilson
Dan Wilson
Model-Glue:Gesture for Fun and Profit
Watch Video
Presentation Files
DataCurl LLC, My Company
2010 Speaking Engagements
Video Interviews
DZone Articles
Quirk in MySQL Join Conditions
Posted By : Dan Wilson
October 24, 2011 12:30 PM
I found a quirk in a join condition today that caused too many records to display. Look at the following query and notice the two AND clauses after the LEFT JOIN to activitytype.
2FROM organization grouptable
3INNER JOIN (
4 SELECT c.CommunityID, o.OrganizationID, d.DivisionID
5 FROM
6 community c
7 LEFT JOIN organization o ON c.communityID = o.communityID
8 LEFT JOIN division d ON o.organizationID = d.organizationID
9 WHERE c.communityID = 1
10 ) orgmodel ON ( grouptable.OrganizationID = orgmodel.OrganizationID )
11INNER JOIN member m ON orgmodel.OrganizationID = m.OrganizationID
12LEFT JOIN activity a ON a.memberID = m.memberID
13LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID
14AND hasDistance = 1
15AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14'
16ORDER BY activitydate
This query runs and returns 2918 rows. However, when I audit this data, I get rows back that are outside of the time bounds specified in the BETWEEN clause: ( AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14' ). There is no activityDate column on the table activitytype. There is an activityDate column on the activity table however. This means the query is parsed and executed without MySQL throwing an error, but the expression is not used to limit the number of joined rows. The correct recordset (428 rows) is easily obtained by moving the join condition to the correct join statement.
2FROM organization grouptable
3 INNER JOIN (
4 SELECT c.CommunityID, o.OrganizationID, d.DivisionID
5 FROM
6 community c
7 LEFT JOIN organization o ON c.communityID = o.communityID
8 LEFT JOIN division d ON o.organizationID = d.organizationID
9 WHERE c.communityID = 1
10 ) orgmodel ON ( grouptable.OrganizationID = orgmodel.OrganizationID )
11 INNER JOIN member m ON orgmodel.OrganizationID = m.OrganizationID
12 LEFT JOIN activity a ON a.memberID = m.memberID AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14'
13 LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID
14 AND hasDistance = 1
15ORDER BY activitydate
I hope this helps someone else with their MySQL queries.
There are no comments for this entry.
Add Comment
Subscribe to Comments
Suscribe
Follow Us
Contact
See it's stuff like this why I am glad I have moved on to a noSQL solution; MongoDB. Where the idea of creating a "join" doesn't exist really... It's all about embedding related data directly into the document, or row if you want to call it that.
Although the noSQL solution isn't best for everything, such as complex systems that require transactions (banking, accounting, etc), for most things it's ideal... Hopefully we will be seeing more of a push for it in the future, because I am personally tired of SQL and feel it's time to retire it.