newsletters

all_users.sql

SELECT
users.`name`,
users.uid,
content_type_user_profile.field_user_firstname_value,
content_type_user_profile.field_user_lastname_value,
content_type_user_profile.field_user_displayname_value,
content_type_user_profile.field_user_postalcode_value,
content_type_user_profile.field_user_tks_optin_value,
content_type_user_profile.field_user_agreeterms_value,
content_type_user_profile_newsletter.field_user_newsletter_tpages_value,
node_newsletter.vid AS news_vid,
node_newsletter.nid AS news_nid
FROM
users
INNER JOIN node ON node.uid = users.uid
INNER JOIN content_type_user_profile ON content_type_user_profile.nid = node.vid AND content_type_user_profile.vid = node.nid
INNER JOIN node AS node_newsletter ON users.uid = node_newsletter.uid
INNER JOIN content_type_user_profile_newsletter ON content_type_user_profile_newsletter.vid = node_newsletter.vid AND content_type_user_profile_newsletter.nid = node_newsletter.nid
WHERE
node.type = 'user_profile' AND
content_type_user_profile_newsletter.field_user_newsletter_tpages_value = 1

user_children.sql

SELECT
content_field_user_child_name.field_user_child_name_value AS `name`,
content_field_user_child_date.field_user_child_date_value AS date,
content_field_user_child_expecting.field_user_child_expecting_value AS expecting
FROM
content_field_user_child_date
LEFT JOIN content_field_user_child_expecting ON content_field_user_child_date.vid = content_field_user_child_expecting.vid AND content_field_user_child_date.nid = content_field_user_child_expecting.nid AND content_field_user_child_date.delta = content_field_user_child_expecting.delta
LEFT JOIN content_field_user_child_name ON content_field_user_child_name.vid = content_field_user_child_date.vid AND content_field_user_child_name.nid = content_field_user_child_date.nid AND content_field_user_child_name.delta = content_field_user_child_date.delta
WHERE
content_field_user_child_date.field_user_child_date_value IS NOT NULL AND
content_field_user_child_date.vid = %d AND
content_field_user_child_date.nid = %d
ORDER BY
content_field_user_child_date.delta ASC