drupalcon-session-dump.sql

-- Alias all the fields we want.
SELECT cts.field_session_day_value AS session_day,
       cts.field_session_time_value AS session_time,
       cts.field_session_room_value AS session_room,
       n.title AS session_title,
       pn.title AS presenter_name,
       td.name AS track_name
-- Start with the session table
FROM content_type_session AS cts
-- Get the main node data to eliminate unpublished nodes and the author uid.
INNER JOIN node AS n ON (cts.vid = n.vid AND n.status = 1)
-- Get the node data for the user profile
LEFT JOIN node AS pn ON (pn.uid = n.uid AND n.status = 1 AND pn.type = 'profile')
-- Get the related terms
LEFT JOIN term_node AS tn ON (tn.vid = n.vid)
-- Get the session track name
LEFT JOIN term_data AS td ON (tn.tid = td.tid)
-- Use get selected sessions
WHERE cts.field_session_type_value = 'session'
-- Eliminate duplicate by filtering on the track name vocabulary id
AND td.vid = 23
-- Put stuff in the order it needs to appear in the programme.
ORDER BY session_day, session_time, session_room;