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 = 953 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 107 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
CMK/Czech Master Kits 129-P32001
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
CMK/Czech Master Kits CMK5145
German WWII Aircraft Maintenance Toolbox Our 1/48 4415 Toolbox set is among our best selling items indeed. It simply could not have been overlooked and so we upscaled the set to 1/32 with many more details of course. Suits dioramas of German (as well as other) WW2 aircraft and military vehicles.
Diorama accessories (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
CMK/Czech Master Kits CMQ32412
Hawker Hurricane Mk.I/II Main Wheels (Four Spoke Discs) and Tailwheel 1/32 / (designed to be used with Revell kits) Nicely detailed four-spoke hub wheels with weighted tyres directly replacing the styrene ones of Revell's kit. (designed to be used with Revell kits)
Aircraft exhausts (resin)
Catalogue
£9.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
CMK/Czech Master Kits CMQ32410
Hawker Hurricane Mk.I/II Ejector Type Exhausts 1/32 / (designed to be used with Revell kits) New ejector type exhausts with fine details, a lot better than the kit styrene items. Designed to fit the new Revell's Hurricane model. (designed to be used with Revell kits)
Aircraft seats (resin)
Catalogue
£5.85
-
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
CMK/Czech Master Kits CMQ32411
1:32
Hawker Hurricane Mk.II Fish Tail Type Exhausts 1/32 / (designed to be used with Revell kits) New fishtail type exhausts with fine details, a lot better than the kit styrene items. Designed to fit the new Revell's Hurricane model. (designed to be used with Revell kits)
Aircraft exhausts (resin)
Catalogue
£5.85
-
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
CMK/Czech Master Kits CMQ32406
Yakovlev Yak-9T exhausts (designed to be used with ICM kits)
Aircraft exhausts (resin)
Catalogue
£5.40
-
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
CMK/Czech Master Kits CMQ32407
Yakovlev Yak-9T wheels (designed to be used with ICM kits)
Aircraft wheels (resin)
Catalogue
£5.40
-
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
CMK/Czech Master Kits CMF32381
RAF Westland Whirlwind Fighter Pilot (1942) for Special Hobby Whirlwind and RAF other planes Our figure of a standing RAF pilot is meant especially for the Westland Whirlwind kit but you can use it with other British planes as well. The figure includes detailed cast parts and a small PE part. (designed to be used with Special Hobby kits etc)
Figures (resin)
Catalogue
£13.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
CMK/Czech Master Kits CMK5144
M35 Armament Subsystem Upgrade Set for Bell AH-1G Cobra (designed tone used with ICM and Revell kits) This M35 (adapted Vulcan cannon) set consists of both 3D-Printed and resin cast parts and will significantly improve the levels of detail of the AH-1G kit whose styrene armament parts lack many of the important detail.
Aircraft detailing sets (resin)
Catalogue
£15.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

CMK/Czech Master Kits CMK5122
Mitsubishi A6M5c Zero Armament Set (designed to be used with Hasegawa kits) This set contains resin parts enabling the modeller to open the wing armament bays and show the cannon and machine gun replicas with their ammunition magazines and feed chutes. The set also contains machine gun and cannon barrels plus armament bay access panels.
Aircraft guns (resin)
Catalogue
£19.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
CMK/Czech Master Kits CMK5142
de Havilland DH.82 Tiger Moth Wing Tank Correction Set (designed to be used with ICM and Revell kits) The top wing fuel tank of the Tiger Moth was made of corrugated sheet metal. That simply means it cannot be faithfully reproduced from styrene and a nicely done resin set does come handy here.
Aircraft detailing sets (resin)
Catalogue
£7.40
-
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

CMK/Czech Master Kits CMK5131
Hawker Tempest Mk.II Bristol Centaurus engine (port side profile) For more information on this product, please click on the link to go to the Aircraft In Miniature A port side half of the Tempest's Bristol Centaurus powerplant as used in the Mk.II version of the British fighter aeroplane. The installation in the kit is rather straightforward and despite the open port side engine panels, the profile of the airframe remains undi...
Aircraft engines and propellers (resin)
Catalogue
£19.60
-
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
CMK/Czech Master Kits CMK5137
de Havilland DH.82 Tiger Moth Luggage Box (designed to be used with ICM and Revell kits) The set portrays the open luggage box in the fuselage behind the cockpit. Designed for the ICM's new Tiger Moth kit. The resin parts show the internal structure of the fuselage, open cockpit hatches and a nice leather bag " yes a vital part of any weekend out of a flying instructor and his girlfriend.
Aircraft detailing sets (resin)
Catalogue
£10.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

No Image
CMK/Czech Master Kits CMK5138
de Havilland DH.82 Tiger Moth Correction Propeller (designed to be used with ICM and Revell kits) The set offers new Tiger Moth propeller with corrected shape. For the ICM's new DH.82 kit.
Aircraft engines and propellers (resin)
Catalogue
£3.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

No Image
CMK/Czech Master Kits CMK5139
de Havilland DH.82 Tiger Moth Mainwheels and Tailskid (designed to be used with ICM and Revell kits) The resin cast main undercarriage wheels feature finely rendered and crisp detail as well as the manufacturer's logo. On top of that all, the resin set also brings three styles of the wheel hubs, both on the outboard and inboard side of the wheel. The three styles are as follows " a simple hub without any cover, a hub with a pl...
Aircraft wheels (resin)
Catalogue
£8.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
CMK/Czech Master Kits CMK5140
1:32
de Havilland DH.82 Tiger Moth Instrument Panels with Compasses and Coaming, Correction Set (designed to be used with ICM and Revell kits) This correction set brings two new instrument panels and a pair of compasses for the ICM's new Tiger Moth model. Etched parts and decals included too.
Aircraft detailing sets (resin)
Catalogue
£4.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
CMK/Czech Master Kits CMK5141
Fiat CR.42 Main Wheels (designed to be used with ICM kits) New undercarriage wheels with better detail than the ICM's new Fiat CR.42 kit has.
Aircraft wheels (resin)
Catalogue
£8.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
CMK/Czech Master Kits CMQ32395
Westland Whirlwind Mk.I Gun Barrels These cannon barrels offer nice levels of detail and are a very easy replacement for the styrene ones from the SH Whirlwind kit. (designed to be used with Special Hobby kits)
Aircraft guns (resin)
Catalogue
£4.70
-
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
CMK/Czech Master Kits CMK5134
1:32
Douglas A-26 Invader Wheels Late Type. Detail resin cast wheels for the Hobby Boss models of the A-26 Invader. The kit contains black vinyl items which are not widely favoured by the modellers. (designed to be used with Hobby Boss kits)
Aircraft wheels (resin)
Catalogue
£9.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
CMK/Czech Master Kits CMK5135
Westland Whirlwind Mk.I Superbly detailed resin cast wheels which in fact are much better than those styrene ones in the Special Hobby's Westland Whirlwind Mk.I kit. The new wheels are weighed and unlike the styrene wheels, these come in one piece, so no glueing is necessary. The set also contains a new tail wheel. (designed to be used with Special Hobby kits)
Aircraft detailing sets (resin)
Catalogue
£9.20
-
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
CMK/Czech Master Kits CMK5133
ESK 2000 B German WWII Gun Camera. The German camera gun could be mounted on either the wing or fuselage of the Messerschmitt Bf-109and other single engine fighter planes and was used for combat training. Some other types, as the Bf-110, could carry the gun inside the nose.
Aircraft detailing sets (resin)
Catalogue
£7.40
-
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
CMK/Czech Master Kits CMK5132
Hawker Tempest Mk.V Fuselage Fuel and Oil Tanks. This set offering the open petrol and oil tanks compartments has been made to fit the Special Hobby's Tempest Mk.V as well as the same kit reboxed by Revell. The tanks were placed inside the fuselage, between the engine and the pilot's office. Covering panels come with the set as well. (designed to be used with Revell and Special Hobby kits)
Aircraft detailing sets (resin)
Catalogue
£19.99
-
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

CMK/Czech Master Kits CMQ32292
1:32
Yakovlev Yak-3 exhausts (designed to be used with Special Hobby kits)
Aircraft exhausts (resin)
Catalogue
£4.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

CMK/Czech Master Kits CMK5130
MD-3 Ground Power Unit The MD-3 ground power unit was designed to start up engines of such USAF aircraft as were the B-47, T-33 and many others and was in use throughout the 50s and 60s. The master pattern of the model was designed using 3D software and printed out on a 3D printer. The resin cast parts come with a fret of photo etched details and a sheet of decals. The MD-3 kit is already available in 1/72 (item 7422), 1/48 (i...
Diorama accessories (resin)
Catalogue
£29.30
-
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

CMK/Czech Master Kits CMK5004
Grumman F6F-3/F6F-5 Hellcat interior and cockpit set (designed to be used with Hasegawa kits)
Aircraft detailing sets (resin)
Catalogue
£19.80
-
In Stock
5+

   1   |   2   |   3   |   4   |   5      »      [5]

Search Area Manufacturer Item Type Search

Part Number Scale Keywords Results per Page
Exact Match
Sort by:
Sort Direction:
Added Within last week
last 2 weeks
last month
last 6 months
last year