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 ps.id = 954 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 5,255 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
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
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
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
1:100, 1:144, 1:35, 1:48, 1:72
Formation-keeping lights - modern, 2 sets Scale sizes: 1.7x0.5;3.3x0.5;4.8x0.5 mm
Military vehicle decals
Catalogue
£3.20
-
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
StuH 42 Ausf.G MID PROD. JUL-OCT 1943 HIGHLY DETAILED PLASTIC MODEL KIT PHOTO-ETCHED PARTS INCLUDED INDIVIDUAL TRACKS 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 6 OPTIONS
Military vehicle kits (injection)
Catalogue
£57.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
BACK IN STOCK!!! Boeing/Hughes AH-64D Apache (designed to be used with Takom kits)[JGSDF AH-64D]
3D Acrylic Instruments
Catalogue
£29.90
-
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
Boeing/Hughes AH-64E Apache (designed to be used with Takom kits)
3D Acrylic Instruments
Catalogue
£29.90
-
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
Vickers Machine Gun (WWI) The Vickers machine gun was developed on the basis of the earlier Maxim gun. The design prouded itself with great efficacy as well as almost a hundred percent reliability. These technically perfect Vickers guns were produced in really great numbers both for the army (these were water-cooled) and for the air force, where they were air-cooled. The British army had the Vickers in use well until the end o...
Diorama accessories (resin)
Catalogue
£13.70
-
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
A34 Comets of 23 Hussars, 11 Armoured Division Marking set covering 23 Hussars Comets of 11 Armoured Division. A couple of the tanks had names. Being the senior regiment of 11 AD, all squadron symbols would be red, thus a complete set is provided. The allied recognition star is based on a known overhead view of a 11 AD Comet, the ring being thick. Thus, different from kit samples. Generic WDN digits provided, including pre...
Military vehicle decals
Catalogue
£14.80
-
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
A34 Comets of 3 RTR, 11 Armoured Division Marking set covering 3 Royal Tank Regiment Comets of 11 Armoured Division. Probably the most photographed Comet unit during the war. Almost all had names starting with "C" regardless of which squadron. Some had personalised markings. Several AOS "52" squares and 11 AD Symbol variations were detected among the tanks, so were as faithfully captured as possible for the set. Being the sec...
Military vehicle decals
Catalogue
£16.30
-
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
A34 Comets of 2 Fife & Forfar Yeomanry, 11 Armoured Division. Marking set covering 2 Fife & Forfar Yeomanry Comets of 11 Armoured Division. Several of the tanks had names. Being the junior regiment of 11 AD, all squadron symbols would be blue, thus a complete set is provided. Large tactical numbers were seen at the turret rear of the regiment's Comets, so generics are provided. The allied recognition star is based on a...
Military vehicle decals
Catalogue
£14.80
-
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
4.Pz.Div. Tauchpanzer III Ausf.G & Ausf.H Operation Barbarossa and Prior Covers markings of various 4. Pz.Div. Tauchpanzer III Ausf. G & H versions right up to Operation Barbarossa. Markings prior to Barbarossa are 5 specific 2nd company Ausf Gs & Hs, each bear is slightly different from the other. In other words, while they looked similar from afar, each had unique details and lines. "Grisly-bar" isn't the same as the other ...
Military vehicle decals
Catalogue
£14.80
-
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
7.Pz.Div. Befehlswagen & Pz.Kpfw.III (Ausf.J, Ausf.L, Ausf.M & AusfN) Operation Citadel Marking set focused on Pz.Rgt. 25 of 7. Panzer Division Panzer IIIs that saw action at the Kursk Offensive, 1943. Otherwise known as Operation Citadel. Several of the unit's Befehlwagens (III) and Panzer IIIs were photographed with sAbt 503 Tiger Is rolling along in the background. The unit's Panzer IIIs had unique features, such as pointy...
Military vehicle decals
Catalogue
£16.30
-
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
Schwere Panzerabteilung 507 Tiger IIs sAbt 507 Pz.Kpfw.V Tiger IIs that saw action in the Paderborn area, 1945. Pecular to the unit, like their Tiger Is, the first digit of the turret numbers were larger and stood taller than the second and third digits (platoon and tank). Except that the colours were reversed - white outlines on solid black. Of interest is one of the "402"s featured on this set, which was with single-link tr...
Military vehicle decals
Catalogue
£14.80
-
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

Re-released! WWII .50 CAL ammo box labels (6, 7 & 8)
Military vehicle decals
Catalogue
£5.60
-
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

1:35
Re-printed! IDF Jerry Can Markings
Military vehicle decals
Catalogue
£5.60
-
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

1:35
Reprinted! Israeli Antenna Flags & Flag Patches
Military vehicle decals
Catalogue
£7.70
-
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

Re-released and re-vamped! U.S. Armored Division WWII & Post War Patches (1/35)
Figure decals
Catalogue
£11.70
-
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
1:35
Sikorsky CH-54A "Tarhe" (designed to be used with ICM kits)
Aircraft paint masks (self adhesive)
Catalogue
£13.30
-
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
1:32, 1:35, 1:48
Acrylic paint set Military Equipment of the Armed Forces of Ukraine Acrylic paint set x 6 12ml bottles (designed to be used with ICM kits)
Paint, Acrylic 12ml
Catalogue
£7.40
-
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
1:32, 1:35, 1:48
Acrylic paint set for WWI & WWII Weapon and equipment Acrylic paint set x 6 12ml bottles
Paint, Acrylic 12ml
Catalogue
£7.40
-
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
V3000S/SSM Maultier Einheitsfahrerhaus The V3000S with a 95 hp V8 gasoline engine was launched at the Ford plant in Cologne in 1941. Production continued until the end of World War II, with a total production run of more than 25,000 vehicles. This truck was used by the German army, air force and navy. The vehicle had only rear-wheel drive, so it had insufficient off-road capability. To solve this problem, about 14,000 of thes...
Military vehicle kits (injection)
Catalogue
£39.80
-
In Stock
2

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
'Always the First.' Air assault troops of the Armed Forces of Ukraine The Air Assault Forces of Ukraine were created in 2017 on the basis of the existing Highly Mobile Landing Troops, which at that time already had its own separate command. According to their purpose, the units and subunits of the Air Assault Forces are divided into airborne and air assault troops; to enhance their strike potential, tank units were added to t...
Figures (injection)
Catalogue
£13.80
-
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
Helicopter Ground Personnel (Vietnam War) The Vietnam War is often referred to as the "Helicopter War," a name that emerged as a result of their active participation in the armed conflict in Indochina. U.S. Army helicopters performed a wide range of different missions, including troop transportation, reconnaissance, fire support, cargo transportation, and search and rescue of downed aircraft crews. A large number of ground p...
Figures (injection)
Catalogue
£11.80
-
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
AKS-74U The AK47 has higher penetration being an Assault Rifle, while the AK74u has 5% faster movement speed and smaller hipfire crosshairs being a SMG. I would say that the AK47 is superior in midrange combat and farther, while the AK74u is the more useful in close-combat.
Diorama accessories (resin)
Catalogue
£8.60
-
In Stock
5+