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=362554 AND ps.id = 957 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 129 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
Mark I Models MKA14429
Watts 2-bladed RH propeller (2 pcs) This set contains two right-handed propellers, made in plastic. They are designed to be used with Hawker Hurricane kits.
Aircraft detailing sets (injection)
Catalogue
£3.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

No Image
Mark I Models MKA14430
1:144
Watts 2-bladed LH propeller (2 pcs) This set contains two left-handed propellers, made in resin. They are designed to be used with Mark I Gladiator kits.
Aircraft detailing sets (resin)
Catalogue
£3.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

No Image
Mark I Models MKM144172L
Hawker-Siddeley HS.748/BAe.748 'In Civilian Skies' re-release, new decals, Hawker Siddeley HS.748/British Aerospace BAe.748 aâé-~In Civilian Skies (British Airways, Emerald Airways, West Air Sweden) The HS.748 was a British short/medium-range twin-engined airliner designed during the late 1950s. It was originally developed and initially produced by A.V. Roe & Co., then after its dissolution by Hawker Siddeley ...
Aircraft kits (injection)
Catalogue
£30.50
£27.45
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

No Image
Mark I Models MKM144174L
Wessex Wessex HAS.1 'Early' re-release, new decals. The Westland Wessex was a general purpose duties helicopter produced by Westland Aircraft Ltd. and represented a British licence development of the Sikorsky S-58. The initial production version, the Wessex HAS.1, first flew in June 1958 and the type began to be operated by the Royal Navy three years later. The Wessex was designed for ASW, search and strike and air/sea rescue...
Aircraft kits (injection)
Catalogue
£16.80
£15.12
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

No Image
Mark I Models MKM144176L
1:144
Gloster Gladiator 'Mediterranean & Middle East' re-release, new decals. The Gloster Gladiator was a British-built biplane fighter developed from the Gauntlet and its prototype, designated the SS.37, flew in September 1934. The first production model was the Mk.I, which became operational in January 1937. An improved Mk.II version followed in 1938 and eventually 270 aircraft of this Mark were built. The Gladiator was a single...
Aircraft kits (injection)
Catalogue
£17.99
£16.19
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

No Image
Mark I Models MKM144139
1:144
DHC-6 Twin Otter 'In the Americas' Colour schemes included in the kit: 1) de Havilland Canada UV-18B (Series 300) Twin Otter, 77-0465 (MSN 554), AF 77465/98 (wearing WWII heritage D-Day scheme), Wings of Blue Parachute Demonstration Team, 98th Flying Training Squadron (FTS), 306th Flying Training Group (FTG), USAF Academy (USAFA), U.S. Air Force, Peterson AFB, Colorado, U.S.A., 2020-22 2) de Havilland Canada CC-138 (DHC...
Aircraft kits (injection)
Catalogue
£23.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

No Image
Mark I Models MKM144140
DHC-6 Twin Otter 'Worldwide Military Service'
Aircraft kits (injection)
Catalogue
£23.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

No Image
Mark I Models MKM144141
DHC-6 Twin Otter 'Twotter'
Aircraft kits (injection)
Catalogue
£23.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

No Image
Mark I Models MKM144142
1:144
DHC-6 Twin Otter 'In Civilian Skies'
Aircraft kits (injection)
Catalogue
£23.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

No Image
Mark I Models MKM144143
1:144
DHC-6 Twin Otter 'Holiday Season' Colour schemes included in the kit: 1) de Havilland Canada DHC-6 (Series 300) Twin Otter, 9N-AET (MSN 619), Tara Air, 'Great Himalaya Trails', Tribhuvan International Airport, Nepal, 2010-22 2) de Havilland Canada DHC-6 (Series 300) Twin Otter, F-OIQP (MSN 715), 'Aimeho', Air Moorea " Tahiti Moorea Airline, Faa'a International Airport, Tahiti, French Polynesia, 2008-10 3) de Havillan...
Aircraft kits (injection)
Catalogue
£23.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

No Image
Mark I Models MKM144171L
1:144
de Havilland Vampire FB.5/9 'Special Schemes' Description The de Havilland Vampire was a British jet fighter developed during WWII. It was the second jet, after the Gloster Meteor, operated by the RAF. It replaced wartime piston-engine fighters and was in front-line service until 1953. The Vampire Mk.5, which flew in June 1948, was a fighter-bomber modified from a Vampire F.3 fighter, while the Vampire FB Mk.9 was a tro...
Aircraft kits (injection)
Catalogue
£15.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

No Image
Mark I Models MKM144173L
Sikorsky H-34 'Special Service' Colour schemes included in the kit: 1) Sikorsky HSS-1 (SH-34/UH-34G) Seabat, BuNo 141602 (c/n 58-265, G-17-1), Black G17-1, Westland Aircraft trials a/c, RNAY Fleetlands, Hampshire, and Yeovil airfield, Somerset, U.K., June 1956; Westland Wessex WS-58 prototype (Sikorsky HSS-1), XL722, Westland Aircraft trials a/c, A&AEE Boscombe Down, Wiltshire, U.K., summer 1956 2) Sikorsky HUS-1 (UH...
Aircraft kits (injection)
Catalogue
£20.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

No Image
Mark I Models MKM144170L
Dornier Do-17Z 'Battle of Britain' bagged
Aircraft kits (injection)
Catalogue
£22.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
Mark I Models MKM144122L
Hawker-Siddeley HS.748/BAe.748 'Civil Livery' re-release, now bagged
Aircraft kits (injection)
Catalogue
£28.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
Mark I Models MKM144168L
Dassault-Mirage III & 5 'Canards' bagged
Aircraft kits (injection)
Catalogue
£18.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

No Image
Mark I Models MKM144169L
Westland Wessex HU.5 'In Commando Role' bagged
Aircraft kits (injection)
Catalogue
£16.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
Mark I Models MKM144136
de Havilland Sea Venom FAW.22/53/TT.53 'Far East & Australia' (2in1) The de Havilland Sea Venom was a British jet all-weather fighter developed for the Royal Navy from the RAF's Venom NF.2 night fighter. First flown in April 1951, the Sea Venoms were produced as the FAW.20 (50 a/c), improved FAW.21 (167 a/c) and uprated FAW.22 (39 new built and other Mk.21s converted). In total, 256 aircraft were taken on FAA's charge, while 3...
Aircraft kits (injection)
Catalogue
£18.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
Mark I Models MKA14428
Hawker Sea Fury Weapon Set (resin parts: 6 pcs 60lb RP, 2 pcs 1000lb bomb) (designed to be used with MARK I Models kits)
Aircraft detailing sets (resin)
Catalogue
£8.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
Mark I Models MKM144123
de Havilland Mosquito NF/F/PR.II 'Intruder' new plastic parts (propellers)
Aircraft kits (injection)
Catalogue
£21.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
Mark I Models MKM144125
de Havilland Mosquito FB.VI 'Commonwealth Service' new plastic parts (propellers)
Aircraft kits (injection)
Catalogue
£21.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
Mark I Models MKM144158
Hawker Sea Fury F.X/FB.11/F.50 'Early Schemes' (2in1) new mould
Aircraft kits (injection)
Catalogue
£20.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

No Image
Mark I Models MKM144159
Hawker Sea Fury FB.11/FB.60 'Commonwealth Service' (2in1) new mould
Aircraft kits (injection)
Catalogue
£20.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

No Image
Mark I Models MKM144160
Hawker Sea Fury FB.11 'Mediterranean & Middle East' (2in1) new mould
Aircraft kits (injection)
Catalogue
£20.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

No Image
Mark I Models MKM144161
Hawker Sea Fury FB.11 'Far East' (2in1) new mould
Aircraft kits (injection)
Catalogue
£20.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

No Image
Mark I Models MKM144164
Hawker Sea Fury 'Special Schemes' x 1 kit new mould
Aircraft kits (injection)
Catalogue
£17.30
-
In Stock
5+

   1   |   2   |   3   |   4   |   5      »      [6]

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