Hello,
Wrote this earlier today after few related questions was asked in IRC lately.
http://lazylegs.info/articles/modx-revolution-and-sql-(xpdo).html
The information might not be 100% correct (I set warning in the article somewhere also) but gives few heads up understanding joins and how to build complex queries "old" fashioned way. Specially for those who miss in MODX Revo the now deprecated $modx->db(); functions.
Cheers
