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 = 956
		 AND P.product_category_id = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



Found 16 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

Special Hobby SH72170
Nakajima Ki-43-II Oscar
Aircraft kits (injection)
Limited Availability
£16.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

Special Hobby SH72248
Yakovlev Yak-23 Flora 'Red & White Stars'
Aircraft kits (injection)
Limited Availability
£14.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
Special Hobby SH72352
1:72
Dassault-Mirage IIICJ The Mirage IIICJ became the symbol of the Israeli air superiority during the wars with the neighbouring Arab countries. The Mach Two, high performance, delta wing fighter aeroplanes were produced by French Dassault, but the Israeli CJ version differed from the airframes built for the French military by not being fitted with the rocket booster motor. The Israeli Mirages took part in the War over Water, Six...
Aircraft kits (injection)
Limited Availability
£18.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

Special Hobby SH72260
Seversky P-35 'Silver Wings Era' The late 1930s is nostalgically the beautiful era as far as aviation technology is concerned. Polished and with shiny metallic surfaces and having plentiful colour markings distinguishing each unit contemporary photographs. This applies also to the P-35s. Thanks to many photographs in our archives we were able to prepare really varied selection of P-35s with markings of all three squadrons and ...
Aircraft kits (injection)
Limited Availability
£17.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

Special Hobby SH72294
Dassault-Mirage F.1CG Finely detailed new tooling Mirage model comprises six runners, a clear parts runner and nicely cast detailed resin parts for weapon launchers specific only to Greek Mirage F.1s. The decals are printed by Cartograf and supply markings and full set of stencils allowing the modeller to build a silver-painted anniversary machine, two aircraft in standard Aegean Blue and Silver livery or a machine in "Fantasm...
Aircraft kits (injection)
Limited Availability
£16.70
-
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

Special Hobby SH72297
McDonnell FH-1 Phantom "Demonstration Teams and Trainers" (new tooling)The first US Navy's jet aircraft to be operated from the aircraft carriers and the first jet of the U.S. Marines ever, such could be the description of the airframe produced by McDonnell under designation the FH-1 and which was commonly known as the Phantom, although the same name, just with a number II added, would be later given to another and much more p...
Aircraft kits (injection)
Limited Availability
£16.70
-
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
Special Hobby SH72304
Re-released! Short Sunderland Mk.III WWII This is a model of one of the most famous flying boats in the history of aviation. The Mk.III variety was built and saw service during the war times, being introduced to operational use in the turn of 1941-42. Compared with the earlier versions, it featured an improved hull step and was equipped with power-operated gun turrets. Because of its long range capabilities and heavy armament,...
Aircraft kits (injection)
Limited Availability
£49.50
-
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

Special Hobby SH72414
Dassault-Mirage F.1 Duo Pack & Book The 'Viggen' Duo Pack & Book model concept proved to be a real success with the modellers so Special Hobby decide to follow this track and offer the customers another model from this new range which should always contain two complete kits, a publication crammed with unique photos and a few more bonuses as well. The Mirage F.1 Duo Pack contains H.M.H.Publications' exquisite book, one single ...
Aircraft kits (injection)
Limited Availability
£59.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

Special Hobby SH72172
Sikorsky CH-37C Deuce USMC. The CH-37C large transport helicopter powered by two piston engines was developed to the USMC requirements, later however was also used by the US Army. At the time when the type was introduced to service, the CH-37C was the most massive helicopter in world. Both the Marine Corps and Army also operated the Deuce in the Vietnam War. This re-release of the model kit offers one significant improvement...
Aircraft kits (injection)
Limited Availability
£34.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

Special Hobby SH72283
Bell AH-1Q/ S Cobra 'U.S. Army & Turkey' The very first version of the venerable Cobra helicopter, the AH-1G was primarily intended for army direct support. However, the Army command soon realised that dedicated anti-tank helicopters were gravely needed and thus the Q and S versions of the Cobra choppers were developed and also some of the earlier G version machines were upgraded to this new standard, although still having so...
Aircraft kits (injection)
Limited Availability
£15.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
Special Hobby SH72448
Junkers Ju-87D-5 'Axis Satellites' Hungary, Rumania, Bulgaria, Slovak state Academy's top quality styrene injected parts of their Junkers Ju-87G-2 kit has been joined now by Special Hobby's own new conversion parts enabling the modeller to build the original kit in the D-5 variety. On top of that, we have to say that our parts also tackle a few of the trouble spots the Academy kit unfortunately possesses. This version actualy ...
Aircraft kits (injection)
Limited Availability
£18.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

Special Hobby SH72410
1:72
SNCASO SO.4050 Vautour IIN Israeli Air Force/IAF all weather fighter The Vautour IIN was a French twin engine, jet all-weather fighter aeroplane which saw service also with the Israeli Air Force. The 1/72 scale model kit comes on four grey styrene sprues joined by a single clear parts sprue, a bunch of resin parts and photo etches. Highly accurate and detailed model Three differing colour options showing the type throu...
Aircraft kits (injection)
Limited Availability
£28.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

Special Hobby SH72159
Douglas D-558-1 Skystreak (Special Hobby sold out June 2023)
Aircraft kits (injection)
Limited Availability
£16.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

Special Hobby SH72241
1:72
Vickers/CASA type 245 Spanish Vildebeest floatplane. Vickers Vildebeest was RAF's land torpedo bomber version. Spain was interested in this machine since it would fit its Navy Air Force's needs. Spain required installation of Hispano-Suiza 12L in-line engine with 600 Hp (448 kW) power output. According to the Spanish specifications, Vickers designed new version designated Type 245. Two prototypes were built and trialed in Brit...
Aircraft kits (injection)
Limited Availability
£34.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

Special Hobby SH72245
Yakovlev Yak-23 Flora Two-Seater To design a two-seater trainer/ combat version of the common fighter aircraft became almost a tradition in USSR. This tradition with roots in WW 2 was applied to Yakovlev Yak-23, too. Except of the combat version, the two-seater trainer version Yak-23UTI was also designed. It was to serve for transitional and gunnery training but also as fast courier aircraft. The kit contains one sprue with gr...
Aircraft kits (injection)
Limited Availability
£16.70
-
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

Special Hobby SH72310
Avia C-2 The post-war service of the Arados, known as the Avia C.2 in Czechoslovakia, is charted in the first of them. It offers two frames of plastic parts, crystal clear injected canopy and landing lights lenses, detailed resin parts and a set of photo-etched parts. There are three machines offered on the decal sheet, all of them in the natural metal finish, two of them with yellow stripes. One machine is original German Ara...
Aircraft kits (injection)
Limited Availability
£14.20
-
In Stock
4


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