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 pt.id=1812
		 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 68 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

Kora KORC72110
1:72
Mitsubishi Ki-57-II 'Topsy' Japan Palebang Paratrooper (designed to be used with Planet Model kits)
Aircraft conversions (resin)
Limited Availability
£27.99
-
In Stock
1

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

Kora KORC72113
1:72
Mitsubishi MC-21-II French Service (designed to be used with MPM and Revell kits)
Aircraft conversions (resin)
Limited Availability
£27.99
-
In Stock
1

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

Kora KORC7224
1:72
Junkers Ju-86K-2 Hungarian Service part I (designed to be used with Italeri kits)
Aircraft conversions (resin)
Limited Availability
£32.40
-
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

Kora KORC7229
1:72
Junkers Ju-86K/B3A Swedish service part III 'Ski Service' (designed to be used with Italeri kits)
Aircraft conversions (resin)
Limited Availability
£32.40
-
In Stock
1

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

Kora KORCS7263
1:72
Siebel FH-104 Ski (designed to be used with Hasegawa and ICM kits)
Aircraft conversions (resin)
Limited Availability
£14.80
-
In Stock
1

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

Kora KORS72190
1:72
de Havilland DH.82A Tiger Moth Early fuselage (designed to be used with Airfix kits)
Aircraft conversions (resin)
Limited Availability
£7.10
-
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

Multicast MC-72003
Back in stock! Lockheed C-130J-30 Conversion Set RAF (designed to be used with Italeri kits) (ex Wingman) This highly detailed conversion set enables the model maker to convert any Italeri C-130 in a stretched C-130J-C4 of the RAF. Our set contains four fuselage plugs, a revised cargo ramp, a new cargo floor piece, new Antennae and ECM-equipment, finally correctly shaped engines and prop blades, new camera-housings attached to...
Aircraft conversions (resin)
Limited Availability
£76.70
-
In Stock
12

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
Bra.Z Models BZ4042
Vickers Super VC-10/VC10 Conversion inc. fuselage plugs, engines and wing leading edge (designed to be used with Airfix kits)
Aircraft conversions (resin)
Limited Availability
£18.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

Maestro Models MMMK7244
1:72
Saab JA-37 'Viggen' recce nose (designed to be used with Heller kits)
Aircraft conversions (resin)
Limited Availability
£8.70
-
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

Maestro Models MMMK7245
1:72
Saab Sk37 'Viggen' 2-seater updated spine & vac canopies (designed to be used with Heller kits) [Saab JA-37 Viggen]
Aircraft conversions (resin)
Limited Availability
£10.50
-
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

Maestro Models MMMK7279
1:72
S29C Tunnan recce conversion. The S29C Tunnan was converted J29B Tunnan fighters, fitted with five cameras that replaced the guns - so no armament was carried on this version - but it could be fitted with extra fuel tanks or ECM pods. The letter S was derived from Spaning, translating ito reconnaissance in Swedish. A total of 76 aircraft were built from 1954 through 1956. They were later modified with an improved wing design, ...
Aircraft conversions (resin)
Limited Availability
£11.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

Aims AIMS32P013
1:32
Messerschmitt Bf-109G-3 conversion (designed to be used with Hasegawa kits)[Bf-109G-2, Bf-109G-4, Bf-109G-5, Bf-109G-6] Bf-109G-3 flown by Fw. Gƒ¼nther Zick of 2./JG 1, March 1943, Jever, Germany. Note the low down RLM 04 cowl marking. Machine otherwise is in the standard RLM 74/75/76. Photos showing G-1-3 upper wings indicate that early G models still used the F model camouflage pattern. Die Jagdfliegerverbƒ€nde der Deutsch...
Aircraft conversions (resin)
Limited Availability
£14.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

Quickboost (by Aires) QB48215
1:48
Grumman F6F-3E Hellcat conversion set (designed to be used with Eduard kits)
Aircraft conversions (resin)
Limited Availability
£8.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

Quickboost (by Aires) QB72520
1:72
Aero L-29R 'Delfin' reccon conversion (designed to be used with Avant Garde kits) [Aero L-29 Delfin]
Aircraft conversions (resin)
Limited Availability
£8.99
-
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
Aviattic ATT24020
Fokker F.I 103/17 Ltn. Werner Voss resin conversion set and full decals, 'blue painted linen' upper airframe Resin conversion set with full decal scheme to create Voss' iconic F.I 103/17 (designed to be used with Meng Model kits) Set includes full cowling, tail and rudder, gun padding, elevator and rudder control horns particular to the F.I prototypes, designed with Mike Swinburne, Gerry Mos designed 3D-Printed control column...
Aircraft conversions (resin)
Limited Availability
£89.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

Kora KORC7276
Junkers Ju-86E-1 Luftwaffe Conversion set Part II. (designed to be used with Italeri kits)
Aircraft conversions (resin)
Limited Availability
£32.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
Model Maker Decals MD48170
Jan "Donald Duck" Zumbach Planes + resin A-26 nose (designed to be used with ICM Douglas A-26 kits) PZL P.11a. 7.13, "1", 111 FIGHTER SQUADRON WARSAW, POLAND, MAY 1939. 7.13, "1", 111 ESKADRA MYSLIWSKA, WARSZAWA, MAJ 1939. MORANE MS-406 C1. No. 670, L699, "14", DAT Etampes - GC 1/55, Etampes, France, Juni 1940. No. 670, L699, "14", DAT ETAMPES - GC 1/55, ETAMPES, Francja, czerwiec 1940. CURTISS HAWK ...
Aircraft conversions (resin)
Limited Availability
£32.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
Model Maker Decals MD72170
Jan "Donald Duck" Zumbach Planes + resin A-26B nose (designed to be used with Italeri kits) PZL P.11a. 7.13, "1", 111 FIGHTER SQUADRON WARSAW, POLAND, MAY 1939. 7.13, "1", 111 ESKADRA MYSLIWSKA, WARSZAWA, MAJ 1939. MORANE MS-406 C1. No. 670, L699, "14", DAT Etampes - GC 1/55, Etampes, France, Juni 1940. No. 670, L699, "14", DAT ETAMPES - GC 1/55, ETAMPES, Francja, czerwiec 1940. CURTISS HAWK H-75A2. No....
Aircraft conversions (resin)
Limited Availability
£21.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 CMK4013
Hawker Hurricane Mk.II Night Intruder conversion (designed to be used with Hasegawa kits)(discontinued February 2024)
Aircraft conversions (resin)
Limited Availability
£11.30
-
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

Wingman Models WMF32003
McDonnell F-4E/F-4F/F-4G Phantom II Slatted Wing Conversion (ex Isra ISC32025) (designed to be used with Tamiya kits)
Aircraft conversions (resin)
Limited Availability
£31.99
-
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

Wingman Models WMF32004
McDonnell F-4S Phantom Slatted Wing Conversion (designed to be used with Tamiya F-4J kits)
Aircraft conversions (resin)
Limited Availability
£31.99
-
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

AIM - Transport Wings TWC72009
1:72
CF-6 engines conversion pack. When the original 747 was introduced it had Pratt & Whitney JT-9D engines, however may customers asked for aircraft with General Electric C- 6 engines to give commonality with their DC-10 aircraft. Our Transport Wings kit of the 747-100/200 comes with JT-9D, and this conversion pack lets modellers built their model with CF- 6 engines if they wish. The conversion pack contains: Precut polys...
Aircraft conversions (resin)
Limited Availability
£31.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

AIM - Transport Wings TWC72045
RAE Vickers Viscount 838 with Infra Red and SLAR pods (For use with the Mach 2 Vickers Viscount kit and Transport Wings Vickers Viscount 800 conversion set) (designed to be used with Mach 2 Vickers Viscount 700 kits)
Aircraft conversions (resin)
Limited Availability
£17.10
-
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

AIM - Transport Wings TWC72046
Vickers Viscount 700D Slipper Tanks (small) (For use with the Mach 2 Vickers Viscount 700 kit and Transport Wings Dart 510 conversion set) (designed to be used with Mach 2 Vickers Viscount 700 kits)
Aircraft conversions (resin)
Limited Availability
£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
CMK/Czech Master Kits CMK4406
DAP Beaufighter Mk.21 Conversion Set 1/48 This conversion set gives you the option to build the Mk.21 version using Revell's Bristol Beaufighter Mk.X kit. The Mk.21 was built under licence down in Australia. The set consists of resin cast conversion parts, vacuum-formed canopy of the observer and a sheet of decals. (designed to be used with Revell kits)
Aircraft conversions (resin)
Limited Availability
£14.20
-
In Stock
10+

   1   |   2   |   3      »      [3]

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