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_manufacturer_id=7874
		 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 41 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
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
Academy AC12124
1:32
Lockheed-Martin F-16C Fighting Falcon. Air National Guard
Aircraft kits (injection)
Future Releases
£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
Academy AC12126
Bell AH-1W NTS Update (Super Cobra Special) Ex-MRC tooling.
Aircraft kits (injection)
Future Releases
£26.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

Academy AC12283
1:48
Re-isssue!! Boeing CH-46E U.S. Marines 'Bull Frog'
Aircraft kits (injection)
Future Releases
£39.99
£35.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
Academy AC12322
1:48
Lockheed P-38J Lightning USAAF Nose Art
Aircraft kits (injection)
Future Releases
£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
Academy AC12430
1:72
Consolidated PBY-5A Catalina US Navy
Aircraft kits (injection)
Future Releases
£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

Academy AC12536
1:72
Korean-Aerospace-Industries-(KAI) KF-16C Korean Air Force [Lockheed-Martin] MCP
Aircraft kits (injection)
Future Releases
£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
Academy AC12555
1:72
ROKAF T-50B Black Eagles MCP Snap Fit
Aircraft kits (injection)
Future Releases
£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
Academy AC12562
1:72
Boeing B-17G Flying Fortress
Aircraft kits (injection)
Future Releases
£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
Academy AC12625
Boeing/Hughes AH-64D/DJ Apache Helicopter American-built advanced multi-role combat helicopter. Reboxed Revell/Ace Corporation kit with new US Army and Japanese Ground Self-Defence Force decals. Markings for 3 aircraft: (1) US Army Boeing/Hughes AH-64D 01-5241, C-1-4 ATKHB "Sidewinders", 4ID, Iraq 2003; (2) US Army Boeing/Hughes AH-64D 99-5118, C-1-3 Attack Helicopter Battalion "Vipers", 3ID, Iraq 2003; (3) JGDSF Air Tra...
Aircraft kits (injection)
Future Releases
£8.99
£8.09
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
Academy AC12636
Tuploev Air Force Tu-22M3 Backfire C Reboxed Minicraft kit with new decals. Choice of 2 markings: (1) Tu-22M3 'Red 12', Russia 2010; (2) T-22M3 'Red 44', Ukraine Air Force ca.1994.
Aircraft kits (injection)
Future Releases
£12.99
£11.69
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
Academy AC12637
1:144
Lockheed EC-121 Warning Star, ca.1950s-70s Reboxed Minicraft kit with new decals. Choice of 4 markings: (1) USAF RC-121C 51-3836, Hamilton Field, ca.1955; (2) USAF RC-121D, late 1950s; (3) USAF EC-121R 67-21487 "Da Nang Glider", 553rd RS (553rd RW), Korat RTAFB, Thailand, June 1969; (4) USAF EC-121R 67-21498, 553rd RS (553rd RW), Korat RTAFB, Thailand, December 1970. Licensed by Lockheed Martin.
Aircraft kits (injection)
Future Releases
£14.99
£13.49
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
Academy AC12638
Boeing KC-135R Stratotanker, Reboxed Minicraft kit with new decals. Choice of markings for 2 aircraft: (1) KC-135R 58-0018, 22nd ARW, 931st ARG, McConnell AFB 2013; (2) KC-135R 58-0023, Illinois Air National Guard ca.2005. Licensed by Boeing.
Aircraft kits (injection)
Future Releases
£18.99
£17.09
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
Academy AC12640
Boeing USAF KC-97L Stratofreighter, ca.1950-70s
Aircraft kits (injection)
Future Releases
£18.99
£17.09
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
Academy AC12645
Boeing/Hughes AH-64D/DJ Apache Helicopter American-built advanced multi-role combat helicopter. Reboxed Revell/Ace Corporation kit with new US Army and Japanese Ground Self-Defence Force decals. Markings for 3 aircraft: (1) US Army AH-64D 01-5241, C-1-4 ATKHB "Sidewinders", 4ID, Iraq 2003; (2) US Army AH-64D 99-5118, C-1-3 Attack Helicopter Battalion "Vipers", 3ID, Iraq 2003; (3) JGDSF Air Training School AH-64DJ JG-4501...
Aircraft kits (injection)
Future Releases
£8.99
£8.09
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
Academy AC13243
M997 AMBULANCE
Military vehicle kits (injection)
Future Releases
£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

Academy AC13312
1:35
K-9 Self Propelled Artillery
Military vehicle kits (injection)
Future Releases
£27.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
Academy AC13428
German Army Leopard 2A4, ca.1980s-2000s Reboxed Ace Corporation kit. 3 sprues. Choice of 5 markings: (1) Bundeswehr, 361st Pz Battalion, Exercise 'Certain Challenge', West Germany, 1988; (2 & 3) Turkish Army, 2nd Cavalry Armoured Brigade. Turkish-Syrian border, 2016; (4) Swiss Army, Pz 87, 13th Pz Battalion, Steel Army Parade, Thun, 2006; (5) Spanish Army (Ejĩrcito de Tierra), 'Extremadura' XI Mechanized Infantry Brigade...
Military vehicle kits (injection)
Future Releases
£12.99
£11.69
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
Academy AC13527
Sherman M4A3E2 Medium Tank Jumbo Cobra King
Military vehicle kits (injection)
Future Releases
£69.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
Academy AC13530
US Army M4A3E8 Korean War
Military vehicle kits (injection)
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
Academy AC13556
WWII German Panzer I Ausf B & Motorcycle, NEW TOOLING ca.1939/40 German light tank used in the early stages of WWII. Accompanied by Zundapp K750 motorcycle & sidecar. New tooling. Three figures included. Photo-etch parts. Choice of 5 markings: (1) Yellow 133, 4 Panzer Division, Poland 1939; (2) White 223, 3 Panzer Division, France 1940; (3) White 104, Unknown unit, Poland 1939; (4) Red 24, Pz Abt z.b.V. 40 (Panzer Abteilu...
Military vehicle kits (injection)
Future Releases
£29.99
£26.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
Academy AC13562
USMC M4A2 Sherman 75mm 'Pacific Theater' Detailed hull & 75mm turret. Plank armour, deep wading trunks & photo-etch parts included. Selective open or closed hatches.
Military vehicle kits (injection)
Future Releases
£31.99
£28.79
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
Academy AC18080
Unknown
Double-pulley Crane
Miscellaneous
Future Releases
£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
Academy AC18084
Unknown
Sundial
Miscellaneous
Future Releases
£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
Academy AC18131
Pumping Engine (working model)
Miscellaneous
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
Academy AC18138
Mechanical Drum (working model)
Miscellaneous
Future Releases
£14.99
£13.49
Out of Stock
0

   1   |   2      »      [2]

Search Area Manufacturer Item Type Search

Part Number Scale Keywords Results per Page
Exact Match
Sort by:
Sort Direction:
Added Within last week
last 2 weeks
last month
last 6 months
last year