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 = 955 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 312 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
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
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
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
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
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+

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
Lockheed PV-1 'Ventura' Control Surfaces Resin cast control surfaces for Revell's PV-1 Ventura model or as reboxed by Academy. (designed to be used with Accurate Miniatures, Academy and Italeri kits)
Aircraft detailing sets (resin)
Catalogue
£12.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:48
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
£22.50
-
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 (2 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
£7.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
Sow with 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
£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
1:48
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/48th scale
Diorama accessories (resin)
Catalogue
£11.25
-
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
1:48
Dornier Do-17Z - Wing Fuel Tank Panels & Filler Caps (2 pcs.) The set contains wing fuel tanks for the Dornier Do 17Z bomber model. And these tanks also come with partially shown wing inner structure as well as with the removable wing panels and top side filler caps.
Aircraft detailing sets (resin)
Catalogue
£11.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
1:48
Lockheed F-104C Starfighter Wheels (designed to be used with Kinetic Model kits)
Aircraft wheels (resin)
Catalogue
£6.30
-
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
Czech Hedgehog - WWII Anti-tank Barrier (2 pcs) 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 as w...
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
Afrikak Korps Soldier Prodding Unwilling Camel 1/48 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
£22.50
-
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
Douglas SBD Dauntless Dinghy 1/48 / for Acc. Miniartures and Academy kits. Open compartment with the dinghy as in the port side fuselage aft of the cockpit. The cover panel is in two styles. For Accurate Miniatures kit set as well as the re-releases by Academy, Italeri or Eduard. (designed to be used with Academy, Accurate Miniatures, Eduard and Italeri kits) [SBD-1 SBD-2 SBD-3 SBD-4 SBD-5]
Aircraft detailing sets (resin)
Catalogue
£7.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
Douglas SBD Dauntless Control Surfaces Separate control surfaces for Accurate Miniature's SBD Dauntless or the re-releases of the same kit by Academy, Italeri or Eduard. Our parts offer much nicer details. (designed to be used with Academy, Accurate Miniatures, Eduard and Italeri kits) [SBD-1 SBD-2 SBD-3 SBD-4 SBD-5]
Aircraft detailing sets (resin)
Catalogue
£11.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

BACK IN STOCK! Lockheed-Martin F-16C/Lockheed-Martin F-16D Fighting Falcon intake Block 30/40/50 (engine General Electric) (designed to be used with Hasegawa kits)
Aircraft detailing sets (resin)
Catalogue
£12.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! Zetor 25 'Military w/Towbar for Mikoyan MiG-15/MiG-17s' The Zetor 25 tractor was among the first products of the post-war Czechoslovak industry and also the very first tractor to have been manufactured by the Zetor company. Until 1961 when the production was ceased, a total of 160,000 Zetors left the production line of which almost 100,000 had been delivered across both Europe and Asia. We are now launching the ...
Diorama accessories (resin)
Catalogue
£25.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
North-American B-25 Mitchell Pilot Seats 1/48 / for HK, Acc. Miniatures, Academy and Revell kits. Detail pilots seats for the North-American B-25 Mitchell. The co-pilot's seat offers both the short and tall back rests variety. Etched seat belts also included. (designed to be used with Academy, Accurate Miniatures, Hong Kong Models, Monogram and Revell kits)
Aircraft seats (resin)
Catalogue
£7.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
Mitsubishi A6M2 Zero Control Surface Correction Set 1/48 /(designed to be used with Academy kits) The 1/48 scale A6M2 Zero from Academy is a very nice kit indeed. Its only drawback seems to be fabric on the control surfaces that do not look like the real thing at all. So here you have our correction set which we believe will fit the bill much nicely.
Aircraft detailing sets (resin)
Catalogue
£11.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
North-American B-25J Mitchell Wheels/ Cross Tread Pattern (designed to be used with Hong Kong Models kits)
Aircraft wheels (resin)
Catalogue
£7.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
Afrika Korps Soldier Riding Camel 1/48 / 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 F72388 does
Figures (resin)
Catalogue
£22.50
-
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
Towbar for Mikoyan MiG-15/MiG-17 and other jets 1/48 3D-Printed (designed to be used with Airfix, Bronco Models, Hobby 2000 and Tamiya kits etc etc)
Diorama accessories (resin)
Catalogue
£5.85
-
In Stock
5+