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 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 1,007 results
| View per page
Refine your search

Deprecated: Creation of dynamic property Hannants\SearchFilters::$searchDirection is deprecated in /mnt/vault/Sync/Dessol/Sites/hannants.co.uk/php_includes/classes/SearchFilters.php on line 115
Manufacturer & Catalogue No ASC DESC
Scale ASC DESC
Description ASC DESC
Type ASC DESC
Department ASC DESC
Price ASC DESC
Watch
Stock ASC DESC
Buy

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Hawker Hurricane Gun Sight MK.II (2 pcs.) 1/32 Detail gun sight for the new Revell's Hurricane, made of clear resin material. (designed to be used with Fly and Revell kits)
Aircraft detailing sets (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
1:35
Type 92 Japanese Heavy Machine Gun The Type 92 was a Japanese heavy machine gun was a development of the earlier 6.5mm Type 3 gun and its origins can be traced back to the 7.7mm Hotchkiss. It was introduced to production in 1932 and used by the Japanese Army in the war in China and also later during WW2. Those guns captured by the Chinese were then also used against their former owners. Even later in the post war years, the gu...
Military vehicle detailing sets (resin)
Catalogue
£13.70
£12.33
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
Noth-American B-25B/B-25C/B-25D Mitchell Wings Air Intakes Correction Set (designed to be used with Accurate Miniatures, Academy and Italeri kits) This correction set eliminates one of the significant shortcoming of that otherwise well sold model. The air intakes of the real Mitchell are different in each wing and also have distinctive shape. Both these features are represented badly in the model. This also applies to the oil ...
Aircraft exhausts (resin)
Catalogue
£4.99
£4.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
1:48
SS-100 Gigant Front Radiator Mask Highly detailed front radiator grille directly replacing the kit's styrene item, which lacks much of the detail and even the radiator itself. You will find it in this set though. (designed to be used with Tamiya kits)
Military vehicle detailing sets (resin)
Catalogue
£8.10
£7.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
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
Sd.Kfz.251D MG 34 with armor shields and pivot mount 1/35 for Tamiya and Dragon kits The German Sd.Kfz.251 armored personnel carrier was most often equipped with a pair of machine guns, MG 34 or MG 42. The forward firing one had a shield, the other was placed on a hinge in the rear part and was used not only against ground targets but also against planes. Sd.Kfz.251D used a machine gun with a different type of shield than th...
Military vehicle detailing sets (resin)
Catalogue
£11.50
£10.35
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
Vickers Machine Gun (WWII) The Vickers machine gun was developed on the basis of the earlier Maxim weapon. 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 e...
Military vehicle detailing sets (resin)
Catalogue
£13.70
£12.33
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
PPSh-41 Soviet Submachine Gun. The iconic 'Shpagin' PPSh-1 submachine gun is among the most-well known guns of WW2 and now our 3D-Printed set almost oozes with plentiful detail and also offers two styles of the ammo magazines, the rounded and the segment types. This weapon remained in service on the Eastern Front not just by the Red Army, but also by the Wehrmacht which captured several of them. In the post war era, the Shpagi...
Military vehicle detailing sets (resin)
Catalogue
£7.20
£6.48
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
Lockheed PV-1 'Ventura' Pilot Cockpit. Faithfully reproduced office area of the pilot and co-pilot of Revell's PV-1 Ventura kit or as reboxed by Academy. Along with the resin parts, the set offers etches and a instrument panel decal. (designed to be used with Accurate Miniatures, Academy and Italeri kits)
Aircraft detailing sets (resin)
Catalogue
£21.40
£19.26
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
MG 34/41 German WWII Machine Gun 1/35 (2 pcs.) The MG34 machine gun was produced in several varieties, the first of which to be mass produced was the MG34/41. Our 3D-Printed MG34 oozes with very nice detail and differs from the later styles by having a shorter barrel, cocking mechanism and the trigger mechanism shape.
Diorama accessories (resin)
Catalogue
£7.99
£7.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
MG 34 Copula Anti Aircraft Machine Gun Ralk 1/35 (2 pcs.) 3D-Printed set of the MG34 machine gun on an anti-aircraft mount as used on the Pz.Kpfw.IV Ausf.H/Ausf.J, Panther and King Tiger tanks. A direct replacement of the styrene machine gun as found in the tank model kits. We offer two machine guns in our set, differing by the barrel, one has its barel perforated, the other one is with plain barrel. During the war both these ...
Military vehicle detailing sets (resin)
Catalogue
£7.99
£7.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
Sd.Kfz.251C MG 34 with armor shields and pivot mount 1/35 for Academy and AFV kits The Sd.Kfz.251C armoured personnel carrier was most often equipped with a pair of machine guns, a forward mounted MG34 with a shield and a rear one of the same type on an anti-aircraft mount. The styrene machine guns from the kits lack many details due to the injection technology. Our 3D-Printed replicas offer much better levels of the detail.
Military vehicle detailing sets (resin)
Catalogue
£11.50
£10.35
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
E-3 German WWII Trailer .German WW2 two-axle trailer that was widely used to transport all sorts of cargo. Towed by many various types of Wehrmacht vehicles such as the SS-100, Opel Blitz lorries or even in as a stop gap measure by Sd.Kfz.250 or Sd.Kfz.251 armoured vehicles.
Military vehicle kits (resin)
Catalogue
£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
Coarse brass chain with rounded links - suitable for 1/35 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:35, 1:48
Medium brass chain with rounded links - suitable for 1/35 and 1/48 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: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
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
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
£22.50
-
In Stock
5+