How to get node content in an sql query?

Question

I see that in D6 the node table has only the title of the node. How to query database to get the body (content) of a particular node?

Update: I figured out the content bodies are stored in node_revisions table.

Answer 1

Drupal 7

In Drupal 7 body content is saved in field_data_body and field_revision_body tables.

So example query to fetch title and body would be:

SELECT title, r.body_value, n.changed FROM node n LEFT JOIN field_data_body r ON n.nid = r.entity_id AND n.vid = r.revision_id

To query via drush, run drush sqlq following by above query.

Answer 2

For education purposes, this is the sql that D6 Views produces (according to its sql preview):

This particular node would be the one having node id 1:

SELECT node_revisions.body AS node_revisions_body,
       node_revisions.format AS node_revisions_format
FROM node node
LEFT JOIN node_revisions node_revisions 
ON node.vid = node_revisions.vid 
WHERE node.nid = 1

In a module, however, you should use proper methods such as db_query() for maintainability and security.