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 pt.id=1899 AND P.price_set = 1 
		 AND P.product_category_id = 105 
		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 
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 116 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
Delivered 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
French Army Char Leclerc, late 1990s The first main battle tank independently designed by France. Reboxed Ace Corporation kit. 4 sprues. 120mm CN120-26/52 main gun & 7.62mm AAT-52 NF1 machine gun. Choice of 5 markings: (1, 2, 3) Task Force Leclerc, KFOR, Kosovo, 1999; (4, 5) Leclerc T5, France.
Military vehicle kits (injection)
New Arrivals
01/05/2024
£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
APRIL RELEASE!!! M3 STUART LIGHT TANK, INITIAL PROD. BOX CONTAINS: HIGHLY DETAILED MODEL OF LIGHT TANK PE PARTS ARE INCLUDED CLEAR PLASTIC PARTS INCLUDED ALL HATCHES CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION METAL SURFACES AND WELD LINES ACCURATELY REPRESENTED IN THE MODEL DECAL SHEET FOR 4 OPTIONS
Military vehicle kits (injection)
New Arrivals
01/05/2024
£52.99
£47.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
APRIL RELEASE!!! IRAQI T-55 AL FEW/ENIGMA. SOVIET MADE BASE BOX CONTAINS: HIGHLY DETAILED PLASTIC MODEL KIT CLEAR PLASTIC PARTS PHOTO-ETCHED PARTS INDIVIDUAL TRACKS INCLUDED METAL SURFACES AND WELD LINES ACCURATELY REPRESENTED IN THE MODEL COLOR SHEET FOR 2 OPTIONS
Military vehicle kits (injection)
New Arrivals
01/05/2024
£74.99
£67.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
APRIL RELEASE!!! Sturmgeschutz/StuG.III Ausf.G April 1943 Production
Military vehicle kits (injection)
New Arrivals
01/05/2024
£21.99
£19.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
Pz.Kpfw 38(t) Ausf.D German light tank will be released April 18, 2024 Attention!!! The booklet is in Polish and English.
Military vehicle kits (injection)
New Arrivals
30/04/2024
£9.50
£8.55
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
German WWII Jagdpanther G1 Early SdKfz 173 with Zimmerit Limited Edition WWII German Tank Destroyer. Zimmerit for upper hull surfaces. Hatches can be fitted opened or closed. Link & length tracks with track assembly jig. Photo-etch parts. Choice of 6 markings. This version of kit ref 2125 is without interior.
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Pz.Kpfw.VI Tiger I Mid-production with Zimmerit The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Hatches can be fitted opened or closed. 2 types of link & length tracks with track assembly jig. M...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Pz.Kpfw.VI Tiger I Late/Late Command w/ Zimmerit 2 in 1 The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Build two ways, Late version or Late Command version. Hatches can be fitted opened or clos...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Pz.Kpfw.VI Tiger I Mid production with Zimmerit & Otto Carius figure Limited Edition The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Otto Carius figure included. Hatches can be fitted opened or ...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Pz.Kpfw.VI Tiger I Big Box Limited Edition (3 tanks 2 figures) Takom 10th anniversary 2013-2023 Tiger I special pack. Big Box includes: Pz.Kpfw.VI Tiger I Mid-production with Zimmerit (TAK02198) Pz.Kpfw.VI Tiger I Late production with Zimmerit (TAK02199) Pz.Kpfw.I Tiger I Mid production with Zimmerit & Otto Carius figure (TAK02200) 1:16 Otto Carius figure (TAK01020)
Military vehicle kits (injection)
New Arrivals
26/04/2024
£139.99
£125.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

German WWII Super Heavy Tank Maus V1 Super-heavy German tank developed during World War II. The Maus was massive, weighing around 188 metric tons. Its armour was exceptionally thick, providing significant protection from enemy fire. It was armed with a 128mm main gun and a coaxial 75mm gun, offering substantial firepower. Despite its powerful weaponry and armour, its mobility was limited due to its immense weight and the resu...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£44.99
£40.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

German WWII Super Heavy Tank Maus V2 Super-heavy German tank developed during World War II. The Maus was massive, weighing around 188 metric tons. Its armour was exceptionally thick, providing significant protection from enemy fire. It was armed with a 128mm main gun and a coaxial 75mm gun, offering substantial firepower. Despite its powerful weaponry and armour, its mobility was limited due to its immense weight and the resu...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£44.99
£40.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

South African G6 Rhino Self-propelled Howitzer The G6 Rhino of the South African National Defence Force (SANDF) is a 155mm self-propelled howitzer developed in the late 1970s. It is based on a 6x6 wheeled chassis equipped with a 155mm howitzer capable of firing a variety of ammunition including HE-FRAG, smoke, illumination and incendiary rounds. Maximum range is 30km with standard HE-FRAG projectile and 39km with rocket-assis...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£52.99
£47.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
1:35
Tiger I QUICK BUILD No Glue! - No paint! - Just BUILD! NEW TOOLING
Military vehicle kits (injection)
New Arrivals
24/04/2024
£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
Sherman Firefly QUICK BUILD No Glue! - No paint! - Just BUILD! NEW TOOLING
Military vehicle kits (injection)
New Arrivals
24/04/2024
£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

1:72
BACK IN STOCK! MGM-52 "Lance", U.S. ballistic SSM on towed launche
Military vehicle kits (injection)
New Arrivals
22/04/2024
£20.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
Pz.Kpfw.VI Tiger I Ausf.E late production. LINK-AND-LENGTH TRACKS OR GLUABLE RUBBER TRUCKS - DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET The Panzerkampfwagen VI Ausf. Tiger I is undoubtedly one of the most famous tanks of World War II. It was developed to provide the armored divisions of the Wehrmacht with a heavily armored and heavily armed tank to complement the more common medium tanks, Panzer III and Panzer IV...
Military vehicle kits (injection)
New Arrivals
19/04/2024
£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
JGSDF AAVP7A1 RAM/RS Amphibious Rapid Deployment Brigade
Military vehicle kits (injection)
New Arrivals
15/04/2024
£24.99
£22.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
JGSDF TYPE16 MCV "Rapid Deployment Regiment"
Military vehicle kits (injection)
New Arrivals
15/04/2024
£26.99
£24.29
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
JGSDF Type 96 Wheeled Armored Personnel Carrier A
Military vehicle kits (injection)
New Arrivals
15/04/2024
£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
US M107 175mm Self-propelled Gun, 1960s-70s Large and powerful artillery piece used by the United States Army and Marine Corps from the early 1960s to the late 1970s. It was designed to provide long-range fire support and it's lighter weight construction permitted air-portability by C-5 Galaxy transport aircraft. New tooling injection plastic 175mm barrel & ammunition with adjustable elevation angle. Simplified driver's stat...
Military vehicle kits (injection)
New Arrivals
12/04/2024
£89.99
£80.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
USMC/ROC AAVP-7A1 w/ EAAK & interior, ca.1989-present Fully tracked amphibious landing vehicle with Enhanced Applique Armour Kit (EAAK) used by the United States Marine Corps and the Republic of China Marine Corps. Plastic injection parts by Hobby Boss with new upgraded resin conversion parts. Choice of markings for USMC and ROC Marines.
Military vehicle kits (injection)
New Arrivals
12/04/2024
£99.99
£89.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
US M3 Grant Medium Tank Armed with a 75mm gun in the main turret and a 37mm gun in the secondary turret, the M3 Grant was first used in combat by the British in North Africa in 1942. 19 sprues, upper &, lower hull, individual track links, photo-etch parts. L: 163.4mm, W: 77mm, Total parts: 640+
Military vehicle kits (injection)
New Arrivals
12/04/2024
£61.99
£55.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
M1014 w/ M747 Semi-trailer Tractor & semi-trailer combination used by the United States military to transport heavy equipment, such as tanks, armoured personnel carriers, artillery pieces etc. 19 sprues, upper &, lower hull, individual track links, photo-etch parts. L: 163.4mm, W: 77mm, Total parts: 640+
Military vehicle kits (injection)
New Arrivals
12/04/2024
£99.99
£89.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
Sd.Kfz.251/22D
Military vehicle kits (injection)
New Arrivals
12/04/2024
£169.99
£152.99
Out of Stock
0