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=8213 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 38 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
Westland Whirlwind FB Mk.I 'Fighter-Bomber' Hi-Tech version. Not long before the outbreak of WW2, the British Air Ministry issued a requirement calling for two types of fighter aircraft, one light weight with machine gun armament and the other one with heavy cannon armament. To these requirements, the machine gun armed Hurricanes and Spitfires were designed, while to fit the second category, the Westland offered their Whirlwi...
Aircraft kits (injection)
Catalogue
£89.99
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Westland Whirlwind Mk.I In the days leading to the outbreak of the Second World War, the British Air Ministry issued requirements calling for two types of a new fighter aircraft, a lighter one armed just with machine guns and the other one with heavier cannon armament. To this specifications, the Spitfire and Hurricane machine gun fighters were developed. The requirement for the heavier type led to the creation of the twin-eng...
Aircraft kits (injection)
Catalogue
£65.99
-
In Stock
7

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
North-American T-2 Buckeye 'Special Markings' T-2 Buckeye 'Anniversary Markings' The T-2 Buckeye jet trainer is no doubt among the very legends of the US naval aviation. Both the US Navy and Marines operated this type for a very long time whilst in Greece, the Buckeye saw service even longer. Besides the standard white and red colour scheme, quite a few US airframes bore much more striking colours. And mostly in 1976 when the ...
Aircraft kits (injection)
Catalogue
£54.40
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
North-American T-2 Buckeye MASK. Pre-cut paint masks for both the outside and inside of Special Hobby's T-2 Buckeye clear parts, Comes with masks for the tyres, too. (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£7.20
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Bloch MB.152C-1 Early Version. The Bloch MB 152C.1 was one of four pivotal fighter aircraft types the Armƒ¯é¿é½e de l'Air (or French Air Force) used in the defence of France in 1940. Alongside the Morane-Saulnier MS 406, Dewoitine D.520 and ex-U.S. Curtiss H 75, they tried, although in vain, to stop the invading German Luftwaffe. As the development of this machine was not an easy one and the production was rather slow, the pro...
Aircraft kits (injection)
Catalogue
£42.50
-
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

Nakajima Ki-27 Otsu Nate Decals Over Malaya and Philippines
Aircraft kits (injection)
Catalogue
£42.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

1:32
Fiat G.50-II in Finnish Service. Finland, like David against Goliath, threatened by Stalin Soviet Union, was purchasing available weapons all over Europe in the years 1938-39. Whole batch of Fiat G.50 fighters was bought in Italy. Despite some troubles with their delivery, the new Fiat fighters made it to Finland just in time to get involved in the Winter War and fought successfully as well as during the Continuation War. As t...
Aircraft kits (injection)
Catalogue
£42.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
Fiat G.50B Bicomando MASK Pre-cut paint masks designed for the clear parts and undercarriage wheels of the Fiat G.50B Bicomando model. (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£5.20
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Fiat G.50 Freccia MASK Pre-cut paint masks for the clear parts and undercarriage wheels of all single seater versions of Special Hobby's Fiat G.50 Freccia kits. (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£5.20
-
In Stock
8

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! Polikarpov I-16 Decals China and Japan. Polikarpov I-16 was one of very successful 1930s monoplane fighters equipped with retractable undercarriage. The production of the first of many subsequent versions had started in 1934. I-16 participated in many conflicts in Europe and Asia since the Spanish Civil War to the Second World War. They were still in use even in 1944 on Eastern Front. Soviet Union offered it '...
Aircraft kits (injection)
Catalogue
£35.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
Fiat G.50B Bicomando The Regia Aeronautica, as well as many other air forces during WW2, used two seat machines converted from standard fighter types for the pilot training purposes. In 1940, the RA faced a difficult task as besides new pilots requiring the pilot training, those many already accustomed to flying in the open cockpit of a biplane needed to learn to fly modern, fast monoplanes with retracting undercarriage. And t...
Aircraft kits (injection)
Catalogue
£43.80
-
In Stock
9

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 AH-1G Cobra 'Marines/US Navy' Hi-Tech Kit The high-quality ICM AH-1G Cobra release comes now to you joined by a rather big number of resin parts, etches and parts made by direct 3D-Printed All these extras makes both the cockpit and exterior of the kit much more busy-looking resulting in a really top-notch release which deserves nothing but the Hi-Tech label. The kit also contains a set of pre-cut masks which is designed ...
Aircraft kits (injection)
Catalogue
£55.80
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Re-released! Morane-Saulnier type N
Aircraft kits (injection)
Catalogue
£35.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

Morane-Saulnier type N 'RFC'
Aircraft kits (injection)
Catalogue
£35.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 AH-1G Cobra 'Early Tails over Vietnam Hi-Tech (ICM kit with new resin and PE-parts. The 1/32 ICM AH-1G Cobra is a very nice kit indeed and here we have made it even much more detailed by the addition of extra large number of resin parts, photo-etches and 3D printer-produced parts. All of these turn the ICM kit into a superbly detailed masterpiece which fully deserves the Hi-Tech title. The kit also contains pre-cut painti...
Aircraft kits (injection)
Catalogue
£55.80
-
In Stock
8

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

Hawker Tempest Mk.II Hi-Tech (There are twice as many resin parts in this kit as in the Mk.V Hi-Tech so it is a slightly higher price)
Aircraft kits (injection)
Catalogue
£83.30
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Westland Whirlwind Mk.I Inside/Outside MASK (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£6.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
1:32
Bell AH-1G Cobra Inside/Outside MASK (designed to be used with ICM and Revell kits)
Aircraft paint masks (self adhesive)
Catalogue
£6.50
-
In Stock
6

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

Heinkel He-100D-1 'Propaganda Jager He 113' The Heinkel He 100D was flown in comparative flight tests against the Messerschmitt Bf-109 type in the competition for the standard Luftwaffe fighter. Eventually, the Bf-109 became the winner, but as the He 100D outperformed it in a couple of respects, it also reached the production, albeit giving only a very small batch of airframes. These were later used by German nazi propaganda t...
Aircraft kits (injection)
Catalogue
£42.50
-
In Stock
8

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/N/P-400 Airacobra (designed to be used with Special Hobby kits) Pre-cut spray mask designed for all P-39/P-400 Airacobra models as kitted by Special Hobby or the Revel re-releases. Besides standard masks for the clear canopy parts, it also offers masks for the wheel hubs.
Aircraft paint masks (self adhesive)
Catalogue
£3.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
Westland Whirlwind Mk.I Mask. Pre-cut painting masks for the canopy and wheels, (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£3.80
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Hawker Tempest MK.II/Mk.V/Mk.VI mask wheels and clear parts pre-cut painting mask for the Tempest kit od all versions (designed to be used with Special Hobby and Revell kits)
Aircraft paint masks (self adhesive)
Catalogue
£3.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

Hawker Tempest Mk.V Decals for NV969 coded SA-A of 486 (NZ) Sqn. RAF, Fassberg base, Germany, April 1945. NV969 was used as personal aircraft of squadron commander, New Zealander S/Ldr. Warren 'Smokey' Schrader, DFC and Bar. The pilot scored, mostly in the cockpit of NV969, 9,5 victories from 10th April 1945 to 1st May1945 becoming the second most successful Tempest pilot (after S/Ldr. Fairbainks).
Aircraft kits (injection)
Catalogue
£49.30
-
In Stock
7

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Yakovlev Yak-3 'Onwards to Berlin' Our Hi-Tech format Yak-3 kit in 1/32 appeared for the very first time in August last year and the kit offered markings for the famous Normandie-Niemen unit. Now we have for you a basic type of the same kit, ie sans the PE and resin parts replacing some plastic items. And of course, this one is also cheaper. The Soviet Yak-3 aircraft is considered one of the best WWII fighter planes to have se...
Aircraft kits (injection)
Catalogue
£35.99
-
In Stock
8

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

1:32
IAR IAR-81 BoPi In the late 1930s, the Polish PZL P.11 and P.24 all-metal, high wing fighter planes were produced under licence by I.A.R., Industria Aeronautica Romana. As it was quite clear that such type of design was nearing obsolescence and the future belonged to low wing fighters with retractable undercarriage and enclosed canopy, a design team led by Ion Grosu was formed with the aim to project a modern warplane of such ...
Aircraft kits (injection)
Catalogue
£47.25
-
In Stock
7