With the release of Drupal 7, many major changes have taken place within the architecture that have drastically altered the code writing experience. The revamped database API has been rewritten with oop principals in mind, allowing the abstraction layer to sit on top of more database types than ever before. I've been working on a project that needed to join multiple tables together, so I thought I'd share a little about my experiences. What I've found is the new api makes it insanely easy to build queries!

In past versions, db queries would look something like the following:
db_query("SELECT n.nid, n.title, n.created FROM {node} n INNER JOIN {users} u ON n.uid = u.uid WHERE blah blah blah

So check this out.. Drupal 7 way:
$query = db_select('node', 'n'); //bring in a table $query
->fields('n', array('nid', 'title', 'created')) //get some fields
->condition('n.type', 'news') //add a condition - the third argument defaults to '='
->join('users', 'u', 'u.uid= n.uid'); //and join in a table
$result = $query->execute();

It looks like it's a bit more work, but it's really not. Writing it out that way cuts down on sql mistakes, and make it easy to comment out a line really quick while you are developing (I seem to do that a lot). Next I wanted to add another joined table, so I added another ->join next to the other expecting it to work magically. Nope! I found out that joins can't be chained like the others due to some aliasing issues, so that's where the addJoin method comes in to play.  

$query->addJoin('INNER', 'users_roles', 'ur', 'u.uid = ur.uid');

and how about another…

$query->addJoin('INNER', 'role', 'r', 'ur.rid = r.rid');

and maybe I want to add in the role name also...

$query->addField('r', 'name');

So we end up with something like this (don't use this code directly without proper testing... duh!) :

$query = db_select('node', 'n');
$query
->fields('n', array('nid', 'title', 'created'))
->condition('n.type', 'news')
->join('users', 'u', 'u.uid= n.uid');
$query->addJoin('INNER', 'users_roles', 'ur', 'u.uid = ur.uid');
$query->addJoin('INNER', 'role', 'r', 'ur.rid = r.rid');
$query->addField('r', 'name');
$result = $query->execute();

Then just run a loop on the $result like usual. So as you can see, you can just keep going and going adding things and Drupal does all the work for you! There is SO much more, I wish I could ramble on for pages... but until next time. 
p.s. did I mention how easy it is to do 'transactions' now? ok ok I'll stop :)