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_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 6,710 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

Scale

Product Type

Manufacturer

Added Within

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

Airone Hobby AHF72019
Bristol Beaufighter Mk.X fabric effect aileron and control surfaces airbrush masks (designed to be used with Airfix kits) these masks are made of a special adhesive vinyl foil called Oramask, which is elastic, semi-translucent and of grey-green colour. The mask can be used several times and is easy to handle before it is final pressed on to the model part surface. (designed to be used with Hasegawa and Hobby 2000 kits) [Mk.VI ...
Aircraft paint masks (self adhesive)
Limited Availability
£5.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

Airone Hobby AHF72021
1:72
Bristol Blenheim fabric effect aileron and control surfaces airbrush masks (designed to be used with Airfix kits) these masks are made of a special adhesive vinyl foil called Oramask, which is elastic, semi-translucent and of grey-green colour. The mask can be used several times and is easy to handle before it is final pressed on to the model part surface. [Mk.I Mk.IV]
Aircraft paint masks (self adhesive)
Limited Availability
£5.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

Airone Hobby AHF72027
1:72
Blackburn Skua Mk.II, Roc Mk.I (designed be used with Special Hobby kits) Fabric effect aileron and control surfaces airbrush masks These masks are made of a special adhesive vinyl foil called Oramask, which is elastic, semi-translucent and of grey-green colour. The mask can be used several times and is easy to handle before it is final pressed on to the model part surface.
Aircraft paint masks (self adhesive)
Limited Availability
£5.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

Airone Hobby AHF72028
1:72
Northrop A-17 family (designed to be used with MPM and Special Hobby kits)[A-17A Nomad Mk.I] Fabric effect aileron and control surfaces airbrush masks These masks are made of a special adhesive vinyl foil called Oramask, which is elastic, semi-translucent and of grey-green colour. The mask can be used several times and is easy to handle before it is final pressed on to the model part surface.
Aircraft paint masks (self adhesive)
Limited Availability
£5.20
-
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

Airone Hobby AHF72045
1:72
Focke-Wulf Fw-190A fabric effect aileron and control surfaces airbrush masks (designed to be used with Eduard kits) these masks are made of a special adhesive vinyl foil called Oramask, which is elastic, semi-translucent and of grey-green colour. The mask can be used several times and is easy to handle before it is final pressed on to the model part surface. [Fw-190A-8/R2 Fw-190A-5]
Aircraft paint masks (self adhesive)
Limited Availability
£3.80
-
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

Star Decals STAR35940
Bulgarian Pz.Kpfw. 35(t)
Military vehicle decals
Limited Availability
£7.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 CMK3081
British Humber Mk.I wheel set (designed to be used with Bronco Models kits)(discontinued February 2024)
Military vehicle detailing sets (resin)
Limited Availability
£8.80
-
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 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

CMK/Czech Master Kits CMK4250
Junkers Ju-88A - Bulkhead with radio sets for B-stand position with two MG 81 (designed to be used with Dragon kits)(discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£8.80
-
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 CMK4306
WWII Drop tanks 600l Luftwaffe(for Ju-88/He-111 for Revell kits) 2 pcs. Set contains 2 new highly detailed WWII Luftwaffe 600L drop tanks, most commonly used on Junkers Ju-88 and Heinkel He-111 bombers. WWII Drop tanks 600l Luftwaffe (for Ju-88/He-111 (designed to be used with Monogram and Revell kits) 2 pcs (discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£9.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

CMK/Czech Master Kits CMK5093
GBU-12 Paveway II Laser Guided Bomb (2 pcs). Set contains 2 highly detailed aerial 500 Lb/ 227 Kg laser guided bombs. The bombs are cast in one piece including the fins with only separate guidance head. The set contains decal sheet with stencils and 20 color photo-etched Remove Before Flight tags. These bombs are in use by USAF, USMC and U.S. Navy, most European air forces, including Czech Air Force and USA, EU / Modern (disco...
Aircraft detailing sets (resin)
Limited Availability
£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 CMK5098
Lockheed F-104G/S NASSAR Radar set 1/32 for Italeri kit. Highly detailed set contains nose mounted NASSAR radar (North-American Search And Ranging Radar). The radar is cast from highly detailed parts including the fuselage bulkhead. The set is intended for one of the best known and widely used U.S. jet aircraft that was flew in USA, Europe, Japan and Canada. (designed to be used with Italeri kits) (discontinued February 20...
Aircraft detailing sets (resin)
Limited Availability
£12.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

CMK/Czech Master Kits CMK5115
Mitsubishi A6M5c Zero Tail Control Surfaces (designed to be used with Hasegawa kits) The set includes a new rudder and tail planes with separate elevators and brings the option to portray the control surfaces in deployed position. No need to modify the kit. (discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£12.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

CMK/Czech Master Kits CMK7067
Daimler-Benz DB-601 Messerschmitt Bf-109E-3/Bf-109E-4 engine (designed to be used with Tamiya kits) (discontinued February 2024)
Aircraft engines and propellers (resin)
Limited Availability
£11.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

CMK/Czech Master Kits CMK7145
Junkers Ju-88A exterior set. Contains control surfaces and life raft stowage compartment (designed to be used with Hasegawa kits) [Ju-88G-6 Ju-88A-14 Ju-88G-1 Ju-88A-8 Ju-88S-1/3 Ju-88C-6 Ju-88T-1 Ju-88A-4 Ju-88R-2 Ju-88G-1/G-6 Ju-88G Ju-88A-11 Ju-88A-4 Ju-88D-1 Ju-88A-6/U Ju-88G-6](discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£12.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

CMK/Czech Master Kits CMK7173
1:72
Messerschmitt Me-262A-1/2 detail set (designed to be used with Academy kits)(discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£31.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

CMK/Czech Master Kits CMK7186
BAC/EE Canberra PR.9 Exterior set (designed to be used with Airfix kits)(discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£12.20
-
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

CMK/Czech Master Kits CMK7204
1:72
Boeing B-17G Flying Fortress Bomb bay set (designed to be used with Revell kits)(discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£12.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

CMK/Czech Master Kits CMK7216
4 x German bomb SC-250 with Kopfring (discontinued February 2024)
Aircraft detailing sets (resin)
Limited Availability
£8.80
-
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 KORA48021
Klemm Kl-25D VII Decals Rumania
Aircraft kits (resin)
Limited Availability
£44.20
-
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 KORA72132
1:72
EFW C-3603 Firefly & C-3603 Lastenrager
Aircraft kits (resin)
Limited Availability
£40.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 KORA72145
1:72
Weiss-Manfred WM-16 Budapest 9
Aircraft kits (resin)
Limited Availability
£39.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 KORA72147
1:72
Weiss-Manfred WM-21B Solyom. 1930s Hungarian light bomber and reconnaissance biplane developed by the Manfred Weiss company from the earlier WM-16 which was based on the Fokker C.V.
Aircraft kits (resin)
Limited Availability
£39.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 KORA72160
1:72
Junkers K 43FA Wheel/Ski (Finnish Service)
Aircraft kits (resin)
Limited Availability
£71.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 KORA72185
1:72
Hawker Australian Demon (War Service)
Aircraft kits (resin)
Limited Availability
£39.99
-
In Stock
1

[1]   «      90   |   91   |   92   |   93   |   94      »      [269]
-50 -20 -10 | +10 +20 +50 +100

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