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 = 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 81 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
Siebel Si-204E 'German Night Bomber & Trainer' The Siebel Si 204A and Si 204D were the transport and trainer versions of the twin engined aeroplane originally built to be operated by the Lufthansa as well as (and mainly) by the Luftwaffe. In the war years, a trainer bomber version was built and later used in the night bombing role. This type was designated the Si 204E and a pair of prototypes and a small batch of Si 204E-0 pro...
Aircraft kits (injection)
Catalogue
£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

No Image
Avro Anson MASK 1/48 Pre-cut spray masks for the clear parts and undercarriage wheels (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£4.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

1:48
Re-released! Supermarine Seafire Mk.III 'Aeronavale & Irish Air Corps'
Aircraft kits (injection)
Catalogue
£23.60
-
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
Avro Anson Mk.I 'Anti-Submarine Annies' as the Avro Ansons were familiarly known then, flew during WW2 not just over their home skies in the UK, they were widely used also in Africa, Canada or Australia. And at the controls could be seen pilots of many various nationalities. And our SH48211 model kit and its colour schemes reminds us of such airmen who left their often occupied homes to fly and fight against the enemy. The fir...
Aircraft kits (injection)
Catalogue
£46.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
1:48
North-American T-2 Buckeye Buckeye 'Anniversary Markings' 1/48 The 1/48 T-2 Buckeye model is back now as is also its larger 1/32 cousin. And both releases share the same colour schemes depicting so called anniversary markings, one scheme is for a US Navy option while the other is more exotic indeed, reminding us that the Buckeyes also flew with the Hellenic Air Force. And both these schemes are quite eye-catching. Unlike the...
Aircraft kits (injection)
Catalogue
£39.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
North-American T-2 Buckeye wheels and canopy frame paint masks Inside/Outside MASK (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£7.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

Re-released! Martin Mk.I / Mk.II Maryland 'Warburtons War' The Martin 167 medium bomber was manufacured in the USA, however it has never been used by the US military. On the other hand, overseas, the French and British showed their interest in the type and placed the orders. In Britain, the type received the name Maryland and while the French aiframes arrived in time to get involved in defending France in 1940, the British one...
Aircraft kits (injection)
Catalogue
£46.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
Supermarine Seafire Mk.XV 'FAA & RCN Service' The Seafire Mk.15 of the immediate post war period was the first ever Seafire powered by the Griffon to see the regular service. Besides the FAA, Mk.15 Seafires also flew with the Royal Canadian Navy. Our model contains also etches and offers two schemes, one as with the British Fleet Air Arm while the other option portrays a machine from Canada
Aircraft kits (injection)
Catalogue
£25.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
Supermarine Seafire Mk.XV MASK (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£4.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
Super Mystere SMB-2 MASK (designed to be used with Azur-Frrom and Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£4.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
Grunau Baby IIB 'German WWII Glider' 1/48 Both in the pre-war years and during the war, the Grunau Baby IIB was the most widely used glider all around the world. It is estimated that about six thousands of these simple airframes might have been built. While before the war, the Grunau Baby gliders were built mainly by amateur pilots using available drawings, with the war on the horizon the type entered almost mass production ...
Aircraft kits (injection)
Catalogue
£15.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

No Image
Grunau Baby IIB Mask Canopy, Fabric Flying / Control Surfaces 1/48 Precut spray masks that fit the fabric-covered, wooden construction wings and empennage of the Grunau Baby glider and using which is painting the various materials almost a piece of cake. (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£12.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

Fokker B.II serie 03.6 'K.u.K'
Aircraft kits (injection)
Catalogue
£24.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

Heinkel He-115B floatplane Huge Heinkel He-115 floatplanes reigned the sea between the UK and Soviet Murmansk in the first phases of the Second World War. German Luftwaffe deployed them during the attack on Norway as well as against the convoys routes. Yet before the war, the He 115 floatplanes were successfully tested in Spain and were exported, by coincidence also to Norway. Norwegian planes were involved in fighting against...
Aircraft kits (injection)
Catalogue
£61.40
-
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-115 Scandinavian Service. The German Heinkel He-115 floatplane won because of its performance the German Navy competition for a torpedo-carrying floatplane. It also set several records in its category in the pre-war times, and also because of this successes, foreign customers became interested in the plane. Sweden and Norway bought the Heinkel floatplanes before the war. While the Swedish machines were to have rathe...
Aircraft kits (injection)
Catalogue
£61.40
-
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

No Image
Piper J-3 Cub The light and cheap J-3 Cub aeroplanes were behind the development of the civil aviation in the USA in the late 1930s. With the end of the world crisis the interest in aviation increased and the J-3 was an ideal type for the basic training and tourist flying. With WW2 coming, the J-3 came into the sights of the military and the USAAF decided to test the type in the artillery spotting and short range recon roles. ...
Aircraft kits (injection)
Catalogue
£23.60
-
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
Piper J-3 Cub Pre-cut paint mask for both the inside and outside of Specia Hobby's J-3 Cub glazing and also for the undercarriage wheels.
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

No Image
Grumman AF-2 Guardian 'Fire Bomber', The largest single engine, piston engine aircraft to have ever been flown from the decks of aircraft carriers " yes this is the apt description of the anti-submarine AF-2 Guardian aeroplane. Following the Korea War, the Guardians were redelegated to reserve units and eventually struck off charge. Some of them, however, and mainly due to the strength of their airframe and their excellent ran...
Aircraft kits (injection)
Catalogue
£40.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

No Image
Grumman AF-2 Guardian MASK (designed to be used with Special Hobby kits) Pre-cut spray masks for the clear parts and undecarriage wheels of the AF-2 Guardian bomber model.
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
VR-1 Helicopter. This decal sheet offers markings for the first example of the VR-1 helicopter as was known the FA 223 built in postwar Czechoslovakia. The VR-1 was delivered to the Police Air Force (SNB " National Security Corps Aviation). And it has also been for the very first time that a helicopter was used for road traffic control. These decals are designed fot Special Hobby's FA 223 kits no SH48020 and SH48201. [Focke-An...
Aircraft decals (military)
Catalogue
£3.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
1:48
Airspeed Oxford Mk.I 'Gunner Trainer' The Airspeed Oxford was a British aeroplane developed early in the war and mainly used in training of multi-engine aircraft crews " pilots, navigators, gunners. The Oxfords saw service with the RAF as well as with air forces of other Commonwealth countries (RNZAF, RCAF, RAAF and SAAF). In the post war years, many Oxfords were also delivered to even more other countries. The Mk.I version of...
Aircraft kits (injection)
Catalogue
£41.60
-
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

1:48
Re-released! Heinkel He-178V-2 The Heinkel He-178 with E. Warsitz at the controls hopped shortly above the runway for the first time on 24 August, 1939 and the real first flight took place only three days later. These were the very first flights of a jet aircraft in the world. On 1 November, while the test flights were being still in progress, the aircraft was displayed to RLM officials. Later on, the aircraft received more po...
Aircraft kits (injection)
Catalogue
£14.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

Back in stock! Phoenix D.II
Aircraft kits (injection)
Catalogue
£24.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

Back in stock! Fairey Firefly FR Mk.I The Initial British Missions Over Korea" Fairey Firefly Mk.I is a typical example of British naval fighter design. Even before the outbreak of the World War 2, Royal Navy asked for two seater long range aircraft designs. Nevertheless, the second crew member was a burden that deprived the performances of these machines. Firefly replaced its low performance Fairey Fulmar predecessor and even...
Aircraft kits (injection)
Catalogue
£35.60
-
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

1:48
Re-released! Fairey Firefly Mk.I Home Fleet
Aircraft kits (injection)
Catalogue
£35.60
-
In Stock
10+