SELECT
	SQL_CALC_FOUND_ROWS families.*,
	CONCAT_WS('.', media.media_id, SUBSTRING_INDEX(media.filename, '.', -1)) AS cname
FROM
	families

LEFT JOIN media ON families.family_id = media.foreign_key_value
LEFT JOIN families__flags ON families.family_id = families__flags.family_id

WHERE
	families.status_id = 'A' AND
	families.type_id = 'F' AND

	families__flags.flag_id = 2 AND

	media.foreign_key_table = 'families' AND
	media.foreign_key_column = 'family_id' AND
	media.label = 'Small Image'

GROUP BY
	families.family_id
ORDER BY
	IF(families.position = 0, 1, 0), families.position, families.name


/*
+-----------+--------------------------------------------------+----------+
| family_id | name                                             | cname    |
+-----------+--------------------------------------------------+----------+
|         5 | Air Shadow?                                      | 280.jpg  |
|        66 | Aire Decor BP200 Builder Series                  | 2481.jpg |
|        64 | Aire Decor BP210 Builder Series                  | 2482.jpg |
|        63 | Aire Decor BP220 Builder Series                  | 2483.jpg |
|        65 | Aire Decor BP230 Builder Series Damp             | 2484.jpg |
|        45 | Andover?                                         | 2117.jpg |
|        52 | Caruso?                                          | 2123.jpg |
|        44 | Celano?                                          | 2118.jpg |
|        57 | Cumulos?                                         | 2310.jpg |
|        47 | Extraordinaire?                                  | 2119.jpg |
|        55 | Hubbardton Forge? - Non-Uplight Fans             | 2160.jpg |
|        46 | Huxley?                                          | 2120.jpg |
|        62 | Inlet?                                           | 2370.jpg |
|        50 | Involution?                                      | 2121.jpg |
|        48 | Kubix?                                           | 2122.jpg |
|        59 | Levon?                                           | 2329.jpg |
|        56 | Limited Edition Custom Enigma?                   | 2199.jpg |
|        61 | Loreto Bay?                                      | 2349.jpg |
|        60 | Medford?                                         | 2410.jpg |
|        58 | Torto?                                           | 2322.jpg |
|        51 | Windpointe? Five Blade Series - Blade Selection  | 2126.jpg |
|        53 | Windpointe? Four Blade Series - Blade Selection  | 2124.jpg |
|        54 | Windpointe? Three Blade Series - Blade Selection | 2125.jpg |
|        49 | Zonix?                                           | 2127.jpg |
+-----------+--------------------------------------------------+----------+
*/