speakers-missing-tickets.sql

-- Start with the session CCK table
SELECT u.mail, n.title FROM content_type_session AS cts
-- JOIN the node table to get users.uid.
INNER JOIN node AS n ON (cts.vid = n.vid AND n.status = 1)
-- JOIN the users table to get e-mail, name, etc.
INNER JOIN users AS u ON (n.uid = u.uid)
-- LEFT JOIN the user role filtering on the DrupalCon attendee role.
-- This means that users WITHOUT this role will have a null value in the result set.
LEFT JOIN users_roles AS ur ON (u.uid = ur.uid AND rid = 5)
-- Filter on accepted sessions and a NULL value in the rid field.
WHERE cts.field_session_type_value = 'session' AND rid IS NULL;