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.price_set = 1 
		 AND P.product_category_id = 102 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE(P.arrival_date) desc, P.code
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 279 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 113
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 AC12361
Beechcraft T-34B Mentor "VT-5 Training Air Wing" Training aircraft used by U.S. Navy. Reboxed Minicraft with new decals. Detailed cockpit & landing gear. Choice of 3 markings: (1) T-34B BuNo 140950, USN Training Sqn 5 (VT-5), 1976; (2) T-34B BuNo 144090, USN Training Sqn 5 (VT-5), 1984; (3) T-34B BuNo 140702, USN Training Sqn 1 (VT-1), Air Station Saufley Field FL, 1976. Masking for canopy included.
Aircraft kits (injection)
Catalogue
£17.99
£16.19
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 AC12631
Lockheed C-130J-30 Super Hercules, 1999-present Four-engine turboprop military transport aircraft designed and built by Lockheed Martin. C-130-J-30 type is a comprehensive update of the Lockheed C-130 Hercules, with extended fuselage, new engines, flight deck and other systems. Reboxing of Minicraft kit with new decals. Assemble with rear ramp open or closed. Multiple choice of markings: (1) USAF 16-5833, 15-5817 or ...
Aircraft kits (injection)
Catalogue
£22.99
£20.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 AC13426
Challenger 1 Mk 3 British main battle tank (MBT) in service from 1983 to 2001. Armed with a 120mm L11A5 rifled gun (retro-fitted with L30A1), 7.62mm L8A2 & 7.62mm L37A2 machine guns mounted on the turret roof. Equipment included virtually impregnable Chobham composite armour for protection against threats including kinetic energy penetrators and high-explosive anti-tank (HEAT) rounds. Other defensive measures included laser w...
Military vehicle kits (injection)
Catalogue
£11.99
£10.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

Academy AC12111
Re-isssue!! Sikorsky UH-60L Black Hawk
Aircraft kits (injection)
Catalogue
£44.99
-
In Stock
10+

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 AC12115
Re-isssue!! Sikorsky AH-60L DAP Black Hawk
Aircraft kits (injection)
Catalogue
£51.99
-
In Stock
10+

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 AC12632
Boeing B-52D Stratofortress Long-range heavy bomber capable of delivering a nuclear payload, developed by Boeing for the United States Air Force entering military service from 1955. The seventy year old design is expected to remain operational until the 2050s. The B-52D variant was used extensively in the Vietnam war. This release from Academy is from the Minicraft tooling. M117 air-dropped demolition bombs included. Markings...
Aircraft kits (injection)
Catalogue
£26.99
-
In Stock
10+

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 AC12703
1:48
Re-isssue!! Sikorsky MH-53E SEA Dragon 'U.S.Navy Mine Hunter and Combat Transport'
Aircraft kits (injection)
Catalogue
£64.99
-
In Stock
7

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 AC13529
German Pz.Kpfw.V Panther Ausf.G Early WW2 German medium tank with 7.5cm main gun and sloped front armour. Photo-etch parts included. Link & length tracks. Choice of 7 markings: (1) 1./Pz.Rgt. 24, 116.Pz-Div., Ardennes, Belgium, Dec 1944; (2) 1./SS.Pz.Rgt 1, Belgium Sep 1944; (3) 10.SS-Pz-Div., Arnswalde, Poland, Feb 1945; (4) 1./Pz.Rgt.24, 116.Pz.Div., France, Aug 1944; (5) 1st SS-Pz-Div., Luxembourg, late 1944; (6) Unknown ...
Military vehicle kits (injection)
Catalogue
£37.99
-
In Stock
5+

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 AC13557
1:35
IDF M113 Zelda APC 1970s-present Israeli variant of the M113 armoured personnel carrier (APC) characterized by its additional armour plating. First introduced in the early 1970s and saw extensive service in the Yom Kippur War of 1973. M113 was replaced by the larger Namer APC from 2004 but Zeldas remain in service with some reserve units. Five crew figures & photo-etch parts included. Can be assembled as either add-on armour...
Military vehicle kits (injection)
Catalogue
£29.99
-
In Stock
5+

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 AC13560
Polish Land Forces K2GF MBT 2023 The K2GF is a variant of the South Korean K2 Black Panther tank ordered by the Polish Land Forces. The K2GF is equipped with a number of features that are specific to the Polish military including new fire control system, Polish-made communication system, and new armour package. The K2GF is also being fitted with Israeli-made countermeasures including the Trophy active protection system. It is...
Military vehicle kits (injection)
Catalogue
£35.99
-
In Stock
5+

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 AC15145
Hyundai Porter II Box Truck, c.2004-present Versatile light commercial vehicle in its 4th generation. The best selling vehicle Korea in 2016. New tooling for Korean market. MCP Multi Colour Parts. Detailed cab interior & chassis. Decals for instruments, badges & number plates. Korean packaging with instructions in English
Car kits (injection)
Catalogue
£35.99
-
In Stock
20+

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 AC15617
1:24
Kia Brisa 4-door saloon car, c.1974-81 Compact 4-door saloon based on Mazda Familia, licence-built by Kia Motors, South Korea's oldest motor manufacturer. Rear wheel drive, 985cc 4-cylinder Mazda petrol engine, 0-60 mph 14.9 seconds. More than 31,000 built from 1974 to 1981. New tooling for Korean market. MCP Multi Colour Parts. Detailed interior with seated driver figure. Detailed floor pan. Decals for instruments, badges &...
Car kits (injection)
Catalogue
£35.99
-
In Stock
16

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 AC12582
McDonnell F-15C ANG "75th Anniversary Medal of Honor" 2019 Special 75th anniversary markings in remembrance of 2nd Lt David R. Kingsley of the United States Army Air Forces, killed in action on 23rd June 1944 when his B-17 Flying Fortress "Sandman" was brought down during a bombing mission over the oil refineries of Ploesti, Romania. 2nd Lt Kingsley was awarded the Congressional Medal of Honor for his gallantry. Kingsley Fiel...
Aircraft kits (injection)
Catalogue
£19.99
-
In Stock
9

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 AC13422
Pz.Kpfw.VI Tiger I Early Version, "Red 131", c.1943 8.8cm KwK 36 main gun & MG34 machine gun. Link & Length tracks. Choice of markings include Tiger I "131", 504th Heavy Tank Battalion, Tunisia; Tiger I "334", 503rd Heavy Tank Battalion, Russia 1943; Tiger I "411", 1st SS Panzer Division, LSSAH Kharkov, Russia 1943.
Military vehicle kits (injection)
Catalogue
£11.99
-
In Stock
11

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 AC13423
German King Tiger "Henschel Turret", 1944/45 WWII German heavy tank. 8.8cm KwK 36 main gun & MG34 machine gun. Link & Length tracks. Choice of 3 markings.
Military vehicle kits (injection)
Catalogue
£11.99
-
In Stock
7

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 AC13425
1:72
USMC M60A1 RISE (P), 1990s Upgraded American medium tank introduced in 1978 with Reliability Improved Selected Equipment (Passive) including Explosive Reactive Armor (ERA) and Kevlar spall liners for the turret. 105mm M68E1 main gun & M2 heavy machine gun. Choice of 3 markings.
Military vehicle kits (injection)
Catalogue
£11.99
-
In Stock
3

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 AC13551
ROK Army K311A1 1é¼ ton utility truck, 1998 to date Current 4x4 light utility truck of the ROK Army. Made by Kia Corp, model name KM450, military designation K311A1, it is a licence-built version of the late 1960s US Kaiser Jeep 'Five Quarter' M715. New tooling. Build with or without rear cover. Includes driver figure, photo-etch parts and window masking. Choice of markings for ROK Army, Air Force & Marine Corps; Philippine ...
Military vehicle kits (injection)
Catalogue
£32.99
-
In Stock
5

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 AC13554
Soviet T-34/85 WWII Medium Tank "Ural Tank Factory No 183" WWII Soviet medium tank. 85mm ZiS-3-S-53 main gun & DT machine gun, photo-etch parts, link & length tracks. Choice of 8 markings.
Military vehicle kits (injection)
Catalogue
£36.99
-
In Stock
5

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 AC12348
Fairchild A-10C Thunderbolt II "75th FS Flying Tigers". NEW TOOLNG IN 2023 Low-flying subsonic ground-attack twin-fanjet aircraft, maiden flight in May 1972. Nicknamed "Warthog" by USAF aircrew, more than 700 A-10s were built from 1973 to 1982 and the type is still in service with the US Air Force. A-10C is the current platform standard with further potential for life-extension; plans to retire the A-10 and replace with the ...
Aircraft kits (injection)
Catalogue
£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 AC12583
German WWII Horten Ho 229 "Wunderwaffe" One of Nazi Germany's "wonder weapons", the jet-powered flying wing demonstrated early characteristics of stealth technology to achieve a smaller radar cross-section than conventional attack aircraft. The project progressed no further than three prototypes built for testing
Aircraft kits (injection)
Catalogue
£18.99
-
In Stock
10+

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 CATAC23
Academy Catalogue 2023
Catalogues and lists
Catalogue
£4.95
-
In Stock
3

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 AC12227
1:48
Mikoyan MiG-29AS Slovakian Air Force (Limited Edition)
Aircraft kits (injection)
Catalogue
£29.99
-
In Stock
1

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 AC13421
Soviet T-34/85 WWII Medium Tank WWII Soviet tank equipped with 85mm ZiS-S-32 main gun & Degtyaryov (DT) machine gun. Choice of 3 markings.
Military vehicle kits (injection)
Catalogue
£11.99
£10.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 AC13254
IDF M-51 Super Sherman
Military vehicle kits (injection)
Catalogue
£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

Academy AC13266
APC M113 Vietnam version (WAS AC1389)
Military vehicle kits (injection)
Catalogue
£29.99
-
In Stock
2

   1   |   2   |   3   |   4   |   5      »      [12]
| +10

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