List of all boats in Drupal SS as per TMW-679.

gistfile1.sql

SELECT
  core_fleet_boats.code as 'Boat Code',
	core_fleet_boats.name as 'Boat Name',
	core_bs_node_extras.search_description as 'Short Boat Description',
	CONCAT('http://www.sunsail.co.uk/', (core_files.filepath)) as 'Layout Image URL', /* note, this is the original file, not web-optimized */
	/* Get the first 4 images of this boat as columns */
	CONCAT('http://www.sunsail.co.uk/', (SELECT core_files.filepath FROM
			core_content_field_gallery,
			core_files
		WHERE
			core_content_field_gallery.nid = core_term_node.nid AND
			core_files.fid = core_content_field_gallery.field_gallery_fid
		LIMIT 1)) as 'Image 1',
	CONCAT('http://www.sunsail.co.uk/', (SELECT core_files.filepath FROM
			core_content_field_gallery,
			core_files
		WHERE
			core_content_field_gallery.nid = core_term_node.nid AND
			core_files.fid = core_content_field_gallery.field_gallery_fid
		LIMIT 1 OFFSET 1)) as 'Image 2',
	CONCAT('http://www.sunsail.co.uk/', (SELECT core_files.filepath FROM
			core_content_field_gallery,
			core_files
		WHERE
			core_content_field_gallery.nid = core_term_node.nid AND
			core_files.fid = core_content_field_gallery.field_gallery_fid
		LIMIT 1 OFFSET 2)) as 'Image 3',
	CONCAT('http://www.sunsail.co.uk/', (SELECT core_files.filepath FROM
			core_content_field_gallery,
			core_files
		WHERE
			core_content_field_gallery.nid = core_term_node.nid AND
			core_files.fid = core_content_field_gallery.field_gallery_fid
		LIMIT 1 OFFSET 3)) as 'Image 4',
	'?' as 'Berths', /* no data to fill here, currently */
	core_fleet_boats.cabins as 'Cabins',
	core_fleet_boats.heads as 'Heads',
	core_fleet_boats.loa_metres as 'Length',
	core_fleet_boats.bem_metres as 'Beam',
	core_fleet_boats.sail_area_metres as 'Sail area',
	core_fleet_boats.max_pax as 'People (max)'
FROM 
	core_fleet_boats,
	/* following for node data */
		core_term_data, /* to get the tid from code*/
		core_term_node, /* to get nid from tid */
		core_node, /*  to get node data from nid */
		/* core_node_revisions,  to get node revision data from vid */
	core_bs_node_extras, /* get short boat description */
	core_content_type_boat, /* get the fid */
	core_files /* get the file name from fid */
	/*core_content_field_gallery */
WHERE
	core_term_data.name = core_fleet_boats.code AND
	core_term_node.tid = core_term_data.tid AND
	core_node.nid = core_term_node.nid AND
	core_node.status = 1 AND
	/* core_node_revisions.vid = core_node.vid AND */
	core_bs_node_extras.i5_code = core_fleet_boats.code AND
	core_content_type_boat.nid = core_term_node.nid AND
	core_files.fid = core_content_type_boat.field_layout_image_fid;