SELECT /* MAIN SEARCH QUERY */
	
	pm.name AS manufacturer,
	pm.name AS manufacturer_name,
	P.id,
	P.id AS product_id,
	P.code,
    P.cost_price,
    P.vat_enabled,
	P.description,
	P.sold_retail,
	P.sold_trade,
	P.sold_wholesale,
    P.country_origin_code,
    P.commodity_code,
    P.price_set,
    P.width,
    P.height,
    P.length,
    P.product_scale_id,
    P.reorder_level,
    P.release_date,
    P.weight,
    P.restriction,
    P.restriction_bit,
    P.active,
    P.minimum_level,
    P.second_hand,
    P.limited_availability,
    P.can_reorder,
    P.warehouse_zone_id,
    P.wholesale_customers,
    P.new_price,
    P.big_postage,
    P.disable_min_order_amount,
    P.last_updated,
    P.notes,
    P.old_product_scale_txt,
    P.has_image_200,
    P.has_image_400,
    P.last_purchase_order_stock,
    P.last_purchase_order_date,
    P.first_delivered_date,
    P.last_moved,
    P.scale_string,
    P.keywords,
    P.finished_date,
    P.availability,
    P.sold,
    P.sold_total,
    P.sold_retail,
    P.sold_trade,
    P.sold_wholesale,
	P.base_price,
	P.base_export_price,
	P.retail_discount,
	P.eu_price,
	P.export_price,
	P.trade_price,
	P.trade_export,
	P.wholesale_price,
	P.wholesale_export,
	P.discount,
	P.backorder_retail_discount,
	P.backorder_discount,
	P.trade_discount,
	P.wholesale_discount,
	P.product_category_id,
    P.product_division_id,
	P.product_type_id,
	P.product_manufacturer_id,
	GROUP_CONCAT(DISTINCT ps.scale ORDER BY ps.scale ASC SEPARATOR ', ') AS scale,
	UNIX_TIMESTAMP(P.date_added) AS date_added,
	UNIX_TIMESTAMP(P.arrival_date) AS arrival_date ,
	DATE(P.arrival_date) AS real_arrival_date ,
	pt.name AS product_type,
	pt.name AS type_name,
	media.filename,
	IF (pp.id is null,0,1) as pending,
	
	if(
		coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) ) , 0 ) >= 0  ,
		if(
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 ) >= P.show_stock,
			P.show_stock ,
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 )
		)
	    , 0
	) as available,
    COALESCE(sl.count,0) as stock_level,
    COALESCE(sc.product_quantity,0) AS stock_committed,
    COALESCE(bv.backorders,0) AS backorders,
    COALESCE(bv.free_backorders,0) AS free_backorders,
	pc.id as department_id,
	pc.name as department,
	pc.name as department_name,
	P.show_stock,
	if(
		pc.name = 'Future Releases',
		true,
		false
	) AS future,
    unix_timestamp(media.date_uploaded) as unixtime



FROM product P
LEFT JOIN stock_level sl on P.id = sl.product_id
LEFT JOIN stock_committed sc on sc.product_id = P.id
LEFT JOIN backorder_values bv ON bv.product_id = P.id
LEFT JOIN product_category pc on P.product_category_id = pc.id
LEFT JOIN product_manufacturer pm ON P.product_manufacturer_id = pm.id
LEFT JOIN product_media media ON P.id = media.product_id AND `media`.`order` = 0

JOIN
product_scale_map AS psm ON (psm.product_id = P.id)

LEFT JOIN
product_scale ps ON (psm.scale_id = ps.id)

LEFT JOIN product_type pt ON P.product_type_id = pt.id
LEFT JOIN pending_product pp ON P.id = pp.id
WHERE 1  AND P.restriction_bit & 1 = 1
		 AND P.product_category_id = 103
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,'%Y-%m-%d %H')   DESC  ,P.code asc 
Future Releases | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Future Releases



Found 2,479 results
| View per page
Refine your search

Deprecated: Creation of dynamic property Hannants\SearchFilters::$searchDirection is deprecated in /mnt/vault/Sync/Dessol/Sites/hannants.co.uk/php_includes/classes/SearchFilters.php on line 115

Scale

Product Type

Manufacturer

Added Within

Manufacturer & Catalogue No ASC DESC
Scale ASC DESC
Description ASC DESC
Type ASC DESC
Department ASC DESC
Price ASC DESC
Watch
Stock ASC DESC
Buy

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Supermarine Spitfire Mk.XIV Low Backs Part 1 1. Spitfire Mk.XIVe NH745/aâé-l"EB Vaâé-, Sqn Ldr D. I. Benham, No 41 Squadron, B.78 Eindhoven, Netherlands, March 1945. 2. Spitfire Mk.XIVe , MV263/aâé-l"GCKaâé-, of Wg Cdr G.C. Keefer, No 125 Wing, B.106 Twente, Netherlands, April 1945. 3. Spitfire Mk.XIVe , MV256/aâé-l"AE Kaâé-, flown by Sqn Ldr D. C. Gordon, No. 402 Squadron (RCAF), B.108 Rheinem , Germany, April 1945. 4. Spitfire...
Aircraft decals (military)
Future Releases
£23.40
£21.06
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:48
Supermarine Spitfire Mk.XIV Low Backs Part 2 1. Spitfire Mk. XIVe , MV257/aâé-l"JEJaâé-, Gp Capt J.E. Johnson, No 125 Wing, B.160 Kastrup, Denmark, May June 1945. 2. Spitfire Mk.XIVe MV260/aâé-l"EB Paâé- of Flt Lt F. A. O. Gaze, No 41 Squadron, B.160 Kastrup, Denmark, May 1945. 3. Spitfire FR Mk.XIVe MV375/aâé-l"DB Eaâé- of No 411 Squadron (RCAF), B.174 Utersen , Germany, late 1945 / early 1946. 4. Spitfire FR Mk.XIVe , TZ198 /a...
Aircraft decals (military)
Future Releases
£23.40
£21.06
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Supermarine Spitfire Mk.XIV High Backs Part 1 1. Spitfire Mk.XIVc NH718/aâé-l"3W Gaâé-, flown by Major K. C. Kuhlmann, No .322 aâé-l"Dutchaâé- Squadron, West Malling , UK, June July 1944. 2. Spitfire Mk.XIVc RM687/aâé-l"DL K Brunhilde aâé-, flown by Flt Lt H. D. Johnson, No. 91 aâé-l"Nigeriaaâé- Squadron, West Malling , UK, July 1944. 3. Spitfire Mk.XIVc RM787/aâé-l"CGaâé-, flown by Wg Cdr Colin Gray, Lympne Wing, Lympne, UK, September 194...
Aircraft decals (military)
Future Releases
£23.40
£21.06
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Supermarine Spitfire Mk.XIV High Backs Part 2 1. Spitfire Mk.XIVc RB159/aâé-l"DW Daâé-, flown by Sqn Ldr R. A. Newbury, No. 610 aâé-l"County of Chesteraâé- Squadron, Friston, Sussex, July 1944. 2. Spitfire Mk.XIVe RM797/aâé-l"EB Eaâé-, flown by Flt Lt W. N. Stowe, No. 41 Squadron, Lympne, UK, October 1944. 3. Spitfire Mk.XIVe RM655/aâé-l"AP Saâé-, flown by Plt Off G. Lord, No. 130 aâé-l"Punjabaâé- Squadron, B.82/Grave, Netherlands, October...
Aircraft decals (military)
Future Releases
£23.40
£21.06
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Kawasaki Ki-61
Aircraft decals (military)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Supermarine Spitfire Mk.XIV (High Backs over Europe
Aircraft decals (military)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Supermarine Spitfire Mk.I - Mk.V National Insignia
Aircraft decals (military)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Hawker Hurricane Mk.I,Mk.II National Insignia
Aircraft decals (military)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Supermarine Spitfire Mk.XIV "High Backs" Over Europe
Aircraft decals (military)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Personal Storms: Wings Commander Hawker Typhoons and Tempest 2nd TAF 1. Hawker Tempest Mk. V (Series 1), JN751/aâé-l"R Baâé-, flown by Wg Cdr R.P. Beaumont, No. 150 Wing, Newchurch , UK, June 1944. 2. Hawker Typhoon Mk.Ib , MN570/aâé-l"Baâé-, flown by Wg Cdr R.E.P. Booker, No. 123 Wing, Thorney Island, UK, June 1944. 3. Hawker Typhoon Mk. Ib , MN666/aâé-l"C Gaâé-, flown by Wg Cdr C.L. Green, No. 121 Wing, Holmsley South, UK, June 1...
Aircraft decals (military)
Future Releases
£16.50
£14.85
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
McDonnell F-4B Phantom II US NAVY VF-84 "Jolly Rogers". 1. F-4B BuNo.151506 AG 200 flown by Cdr Lewis Scott Lamoreaux CO and Lt Richard L. 'Dick Richards of VF-84 Jolly Rogers, USS USS Independence, 1965. 2. F-4B, 151492, AG 206, Delivered to the United States Navy. 1965: VF-84 "Jolly Rogers" as AG-206. VF-154 as NE-x01. 1967: VF-21 "Free Lancers" as NE-101. 12/16/1967: Ditched into the Gulf of Tonkin due to battle d...
Aircraft decals (military)
Future Releases
£15.99
£14.39
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Phantom F-4
Aircraft wheels (resin)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Avro Lancaster
Aircraft wheels (resin)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Junkers Ju-88
Aircraft wheels (resin)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Phantom F-4
Aircraft wheels (resin)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Avro Lancaster
Aircraft wheels (resin)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Junkers Ju-88
Aircraft wheels (resin)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Stencils for Missile Kh-25ML (AS-10 Karen) with death wishes, Ukranian Air Forces
Aircraft decals (military)
Future Releases
£5.99
£5.39
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Dassault-Mirage 2000D (designed to be used with Airfix, Dream Model, Heller, Italeri, ModelSvit, Revell and Tamiya kits)
Aircraft decals (military)
Future Releases
£19.99
£17.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Dassault-Mirage 2000N (designed to be used with Airfix, Dream Model, Heller, Italeri, ModelSvit, Revell and Tamiya kits) 1. Mirage 2000N K2 né°304, EC 2/4 "La Fayette", Luqa, Malta International, July 2011. 2. Mirage 2000N K2 né°318, EC 2/4 "La Fayette", Florennes, Belgium, April 2004. 3. Mirage 2000N K3 né°340, Escadron de Chasse 2/4 "La Fayette", SPA96 "Le Gaulois", operation Barkhane, 2015-2016. 4. Mirage 2000N K3 né°34...
Aircraft decals (military)
Future Releases
£19.99
£17.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Digital Masks for Sukhoi Su-27P aâé-l"Blue 58aâé-, Ukranian Air Forces, digital camouflage Academy, Eduard, Great Wall Hobby, HobbyBoss, Kitty Hawk kits
Aircraft paint masks (self adhesive)
Future Releases
£6.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Sukhoi Su-25 aâé-l"Blue 22aâé- (Former 02), Ukranian Air Forces, Green Clover camouflage KP, Revell kits
Aircraft paint masks (self adhesive)
Future Releases
£6.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Digital Masks for Sukhoi Su-27P aâé-l"Blue 58aâé-, Ukranian Air Forces, digital camouflage IBG Models, Trumpeter kits
Aircraft paint masks (self adhesive)
Future Releases
£4.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
British Armoured Car/Tank Crew Mediterranean - set 1 (3 fig.) (designed to be used with Attack kits)[Humber Armoured Car]
Figures (resin)
Future Releases
£TBA
£TBA
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
LT-40 Light Tank w.37mm gun Profi Line, includes a metal gun barrel and etched parts
Military vehicle kits (injection)
Future Releases
£TBA
£TBA
Out of Stock
0