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=7973 AND ps.id = 956 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 486 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
Hawker Tempest Mk.V Early Gun Barrels The set offers 3D-Printed cannon barrels for the Tempest Mk.V series I models. Our barrels do posses much higher levels of detail compared with the Airfix kit items. (designed to be used with Airfix kits)
Aircraft guns (resin)
Catalogue
£2.70
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Hawker Tempest Mk.V Gun Sight and Seat Correction Set. One of flaws of Airfix's Tempest is the very narrow pilot's seat which is also wrongly shaped. Our correction, 3D-Printed set brings the right size seat which also have much better surface detail. The set contains a clear material 3D-Printed gun sight. . (designed to be used with Airfix kits)
Aircraft detailing sets (resin)
Catalogue
£6.30
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Hawker Tempest Mk.V Exhausts Flawless D3 printed exhausts (designed to be used with Airfix kits) 's Mk.V Tempest models.
Aircraft exhausts (resin)
Catalogue
£3.60
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Hawker Tempest/Typhoon Early type Wheels Detail resin cast wheels with weighted tyres, designed both for the Typhoon models and the early Tempest Mk.Vs. (designed to be used with Airfix kits)
Aircraft wheels (resin)
Catalogue
£4.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:72
Hawker Tempest Late Type Wheels 1/72 Detail resin cast wheels with weighted tyres, designed for models of the late Tempest varieties. (designed to be used with Airfix kits)
Aircraft wheels (resin)
Catalogue
£4.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Hawker Tempest Mk.V Engine and Fuselage Tanks (designed to be used with Airfix kits) For those of you longing to build Airfix's new Tempest model with its power unit and fuel tanks exposed and at the some time not to loose the large radiator housing, a characteristic feature of the Tempest forward fuselage, we have now this 3D-Printed set with really high levels of detail throughout. The set covers the top half of the Sabre en...
Aircraft detailing sets (resin)
Catalogue
£14.85
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:72
RAF (Tempest) Pilot, Dog and Mechanic with Accumulator Trolley. A 3D-Printed set offering a standing RAF pilot with his pet dog and an RAF mechanic handling a accumulator trolley. While the set has been designed for Airfix's Tempest Mk.V, there is no issue employing it with basically any other RAF fighter plane of the mid to late 1940s. (designed to be used with Airfix kits)
Figures (resin)
Catalogue
£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
1:48
Fine brass chain with rounded links - suitable for 1/48 and 1/72 scale
Diorama accessories (brass)
Catalogue
£2.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
Fine brass chain with rounded links - suitable for 1/72 scale
Diorama accessories (brass)
Catalogue
£2.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
1:72
Marder Tank Destroyer Commander and Gunner Two crew figures in this set portray a commander and a gunner of a Marder tank destroyer. Made to fit the Special Armour line Marder II kit made by Special Hobby. In fact, these two can be used with any German WW2 tank destroyers or even another type of military vehicles.
Military vehicle detailing sets (resin)
Catalogue
£8.10
-
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

Messerschmitt Me-163A Control Surfaces Set for Special Hobby/Condor kits 1/72 This set brings separate control surfaces (landing flaps, ailerons, rudder) for a model of the German WWII rocket interceptor aircraft. Intended to be used with the Condor range model or its reissue by Special Hobby. (designed to be used with Condor and Special Hobby kits)
Aircraft detailing sets (resin)
Catalogue
£7.70
-
In Stock
4

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
Concrete Hedgehog - WWII Anti-tank Barrier (4 pcs.) Concrete-made hedgehog anti-tank barriers had been devised by Czechoslovak army specialists only days before the outbreak of WW2. However, due to the shortsighted stance of the democratic powers that gave in to Hitler in Munich in 1938, these barriers were used by the Wehrmacht instead. Our nicely detailed resin parts closely resemble the concrete surface.
Diorama accessories (resin)
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

No Image
Bell P-39Q Airacobra Engine (designed to be used with Arma Hobby kits) Resin cast set which brings you the option to build your Arma Hobby P-39 Airacobra with the port side engine compartment open. Comes with cast engine panel and new exhaust stubs.
Aircraft engines and propellers (resin)
Catalogue
£10.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
1:72
Army Zetor tractor driver and mechanic. Nicely detailed figures of an army tractor driver and an air force ground crew member attaching a tow bar to the tractor so that a MiG-15 fighter could be moved across the tarmac. Designed to be used with our MV128 model of the military used Zetor 25 tractor. (designed to be used with Planet Model kits)
Figures (resin)
Catalogue
£8.10
-
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
Slaughtered pig suspended on a pulley. The most important component of the diorama that shows the usual ends of the domestic pig. The slaughtering and the pig hung over a wooden washtub was a common sight of a rural life. Now it is up to the modeller themselves to decide whether to use the poor beast in a civil or military sort of diorama. The set is completely 3D-Printed to the 1/72nd scale
Diorama accessories (resin)
Catalogue
£8.55
-
In Stock
4

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
Dromedary (2 pcs) A pair of 3D-Printed single-hump camels. Fits dioramas from Africa and Middle East, from the ancient times to the present day, also suitable for American dioramas from 1856 to 1900 " namely depicting the US Conferedarion army, and for Australian dioramas from 1866 to the present.
Diorama accessories (resin)
Catalogue
£9.99
-
In Stock
4

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
Bactrian Camel (2 pcs) A pair of two-hump camels, also known as the Mongolian Camel, made on a 3D printer. Can be used in any diorama from ancient times to the present day.
Diorama accessories (resin)
Catalogue
£9.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
Titan 'World Famous Research and Tourist Submarine' The Titan submersible vessel was used to explore, research, and even transport tourists to the wreck of the Titanic. Regrettably, this submersible gained notoriety in June 2023 when a tragic accident during a tourist expedition to Titanic claimed the lives of everyone aboard. Our model of the submersible is made by means of 3D printing to achieve the best possible detail. The...
Ship kits, full hull (resin)
Catalogue
£39.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
German WWII Aircraft Maintenance Toolbox Really superb-looking, 3D-Printed toolbox that can be used in any diorama featuring aircraft and military vehicles of German or almost any other origin. Fits also very nicely war-prize machines such as Czechoslovak Avia S-199.
Diorama accessories (resin)
Catalogue
£10.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
Czech Hedgehog - WWII Anti-tank Barrier (4 pcs) 1/72 Made of assembled and welded steel beams, this anti-tank obstacle was in fact a Czechoslovak invention originally devised to be used in the border fortifications. When Nazi Germany took over not just the border regions but occupied the whole of our country, the production of these hedgehogs went on but for the Wehrmacht instead. The German army used them on the Western front...
Diorama accessories (resin)
Catalogue
£5.60
-
In Stock
4

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
Afrika Korps Soldier Prodding Unwilling Camel 3D-Printed figure of an Africa Korps soldier forcing his camel to rise to its feet by pulling the bridle, evidently to no avail at all. The beast figure is 3D-Printed, too and it comes with a Tuareg-style saddle.
Figures (resin)
Catalogue
£9.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
Sow and piglets Very nicely 3D-Printed sow with her three youngs. This animaly family will make an unusual basis for a country related diorama or add welcome detail to other types of dioramas, too. It is just about the modeller's creativity.
Diorama accessories (resin)
Catalogue
£7.20
-
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
IAF Dassault-Mirage IIICJ Pilot & Female Ground Crew 1/72. Israel is among the handful of countries whose military rely also upon the women soldiers. Our set contains a pair of figures, one depicts a jet aircraft pilot while the other one is a female member of ground crew. Both of them suit very well our new and no doubt superb model of the Mirage IIICJ delta. Other military planes of that era can be used as a backdrop too, of...
Figures (resin)
Catalogue
£8.10
-
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
PT Boat Weapon Set No.6 - Bofors 40mm AA/AS Gun 1/72 / (designed to be used with Revell kits)s. The 40mm Bofors gun was a standard and universal anti-tank and anti-vessel armament widely used on US Navy boats as well as with other allied fleets. Among other boats, the gun was mounted on the stern of the US PT Boats of the later production standards. Our set is employs several production technologies and contails resin cast par...
Ship detailing sets (resin)
Catalogue
£10.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
Afrika Korps Soldier Riding Camel 3D-Printed . Figure of a German soldier, a member of the Afrikakorps, portrayed as riding a camel in the African desert. The figure as well as the beast are both 3D-Printed , using the same design as set F48389 does.
Figures (resin)
Catalogue
£9.99
-
In Stock
5+