Misc Drupal database queries

This are probably not of general interest, just some example queries from a particular database that I wanted to save here as snippets.

--view all category titles from node: n & d, category node: c
$sql="SELECT n.nid, c.title AS cname, d.field_catcache_value AS cat FROM node AS n"
. " JOIN content_type_document AS d ON d.nid=n.nid AND d.vid=n.vid"
. " JOIN node AS c ON c.nid=d.field_category_nid"
. " WHERE n.type = 'document'";

--get the body of the category definition c from a given node n & d
SELECT n.nid, c.title, r.body AS cname FROM node AS n
JOIN content_type_document AS d ON d.nid=n.nid AND d.vid=n.vid
JOIN node AS c ON c.nid=d.field_category_nid
JOIN node_revisions AS r ON r.nid=c.nid AND r.vid=c.vid
WHERE n.nid=214;

--Fetch a single category defn body from the document node id
$sql="SELECT r.body FROM node AS n JOIN content_type_document AS d ON d.nid=n.nid AND d.vid=n.vid JOIN node AS c ON c.nid=d.field_category_nid JOIN node_revisions AS r ON r.nid=c.nid AND r.vid=c.vid WHERE n.nid=%d;";
print 'Category: ' . db_result(db_query($sql,$nid)) . '';

--loot at first 5 characters of the title of the category c associated with document d
SELECT LEFT(c.title,5), d.field_catcache_value FROM content_type_document AS d JOIN node AS c ON c.nid=d.field_category_nid;

--update all document nodes d from the title of their category node c
$sql="UPDATE content_type_document AS d"
. " JOIN node AS c ON c.nid=d.field_category_nid"
. " SET d.field_catcache_value = LEFT(c.title,5)";

--do if for just one node
<?php db_query("UPDATE content_type_document AS d JOIN node AS c ON c.nid=d.field_category_nid SET d.field_catcache_value = LEFT(c.title,5) WHERE d.nid = %d;",$nid); ?>

--insert extra characters into title field of cat nodes
--first explore, then test, then do
SELECT title FROM node WHERE type='cat' AND title LIKE 'L%';
SELECT CONCAT('L2',SUBSTRING(title,3)) FROM node WHERE type='cat' AND title >= 'L1.22' AND title < 'L1.31';
UPDATE node SET title = CONCAT('L2',SUBSTRING(title,3)) WHERE type='cat' AND title >= 'L1.22' AND title < 'L1.31';

--get certain fields from all webform submissions
$sql="SELECT s.sid, s.uid, s.submitted, df.data AS first, dl.data AS last, LEFT(dc.data,7) AS clinic"
. " FROM webform_submissions s"
. " JOIN webform_submitted_data df ON df.sid = s.sid"
. " JOIN webform_submitted_data dl ON dl.sid = s.sid"
. " JOIN webform_submitted_data dc ON dc.sid = s.sid"
. " WHERE s.nid=3 AND df.cid=1 AND dl.cid=2 AND dc.cid=7 ORDER BY s.sid DESC;";