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

AML AMLC2005
1:32
Americans in Stalin's Sky, Part V Masks - Curtiss P-40C Tomahawk & Bell P-39N Includes decals for 2 aircraft. National Insignia and Canopy paint masks
Aircraft paint masks with decal
Limited Availability
£7.99
-
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

AML AMLD72050
1:72
Soviet Aces in La-5Fé's
Aircraft detailing sets (decal and resin)
Limited Availability
£5.80
-
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

Welsh Models WHSL260P
1:144
Lockheed L100-10 Hercules -vac/resin - Delta Airlines Widget livery. Early Allison engines.
Aircraft kits (resin and vacform)
Limited Availability
£42.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

Noy's Miniatures NM20001
1:200
'Soviet Bomber': A single 1/200 sheet (approx. dimensions: Length: 39.6 cm / 15.6 inch; Width: 28 cm / 11.02 inch).
Diorama accessories (card)
Limited Availability
£7.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

Lifelike LL72022
Focke-Wulf Fw-190 Part 2 (4) Fw-190A-3 Yellow 28 2/EJGr.West; Fw-190A-4 Yellow 4+I 9/JG 2 Oblt Schnell; Fw-190A-4 Black << Maj Walter Nowotny; Fw-190F-8 Yellow 7 I/SG4;
Aircraft decals (military)
Limited Availability
£9.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

Anigrand Craftswork ANIG4047
1:144
Kalinin K-7. Includes BONUS kits of the Kalinin K-12, Moskalyov Sam-7 and Cheranovsky BiCh-17. In 1925, Kalinin made a series of single-engined aircraft characterized by having a quasi-elliptical wing that made his OKB famous in the USSR. In 1930, he designed a gigantic transport aircraft, the K-7 which has seating capacity of 60 or 20 tonnes of cargo. The K-7 design caused attention from the Ministry of Aviation Industry. It ...
Aircraft kits (resin)
Limited Availability
£87.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

Model Alliance ML48139
1:48
BAC/EE Canberra Part 1. Bomber Canopy versions in Foreign Service (5) RRAF163 Royal Rhodesian Air Force 1959 High Speed Silver; R2175 6 Sqn Royal Rhodesian Air Force 1968 or Zimbabwe Air Force 1980 Dark Green/Dark Earth camouflage; WF915 or WF605 75 Sqn RNZAF Malaya 1959; F763/AM Armeĩ de l'Air 1970s
Aircraft decals (military)
Limited Availability
£13.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

Model Alliance ML48147
1:48
Hawker Hurricane in RAF and Commonwealth Service Pt 1 (9) Hawker Hurricane Mk.I L1570/D 3 Sqn 1938; L1568/S 73 Sqn 1938; P Paddy III 73 Sqn 1940; SO-E 145 Sqn 1940; V6873 DT-O 257(Burma) Sqn 1940; Z5236 GO-31 134 Sqn Russia 1941; all Dk Green/Dk Earth. Hawker Hurricane Mk.II BD529 ZY-S 247(China-Brittain) Sqn 1942; Z3971 SW-S 253(Hyderabad State) Sqn 1941, both overall black. BE219 LK-Q 67 Sqn 1942 Dk Green/Ocean Grey/Black
Aircraft decals (military)
Limited Availability
£13.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

Model Alliance ML48153
1:48
BAe Sea Harrier FRS.1 (9) XZ451/100 700A Flight 1976; XZ454/250800NAS 1980; xz483/001 801 NAS 1980; XZ453/105 899 NAS 1981. All NAS Yeovilton. Indian Navy IN601 W/606 INAS300 White Tigers INS Vikrant 1982; IN608/08 Cougar INAS 551B Bravos Op Flying Training Unit (SHOFTU) 2005; IN618/18 300 Squadron 1994; IN618/18 SHOFTU 2005; T-60 2-seaters IN651/651 INAS 552 SHOFTU 2005; IN055/55 INAS552 SHOFTU 2005
Aircraft decals (military)
Limited Availability
£13.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

Model Alliance ML48181
1:48
Wings and Waves Part 2 (6) Fairey Swordfish Mk.I L9780/682 821 Sqn H.M.S. Ark Royal 1939; Fairey Flycatcher N9923/1402 Flt H.M.S. Eagle 1927; Hawker Nimrod S1634/578 802 Sqn H.M.S. Glorious; Supermarine Walrus Mk.I L2190/43 715 Flt H.M.S. Birmingham 1938; Blackburn Skua L3011/L6G 806 NAS 1939; Gloster Sea Gladiator N5500/T6L 769 Sqn Donibristle 1939. Double sheet
Aircraft decals (military)
Limited Availability
£15.35
-
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

Model Alliance ML489020
Westland Sea King Mk.50A (1) Royal Australian Navy HS-817 Great White Sharks 50 Years Anniversary scheme [Sikorsky SH-3H]
Aircraft decals (military)
Limited Availability
£9.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

Model Alliance ML72104
1:72
Panavia Tornado GR.1/GR.4 Part 1. (8) GR.1 ZA370 2(AC) Sqn grey/green camouflage; ZA592/C 2(AC) Sqn grey/white winter camouflage; ZA460 AJ-A 617 Sqn Incirilk 1992 overall desert pink; GR.4 ZA369/U 13 Sqn RAF Lossiemouth; ZD847/AA 9 Sqn Bruggen; ZA542/DM 31 Sqn Bruggen/Marham; ZA542/FJ 12 Sqn Kuwait 1998; All GR.4s in camouflage schemes grey/m/s/grey camouflage; ZD792 SAOEU Boscombe Down in experimental desert pink/grey camo
Aircraft decals (military)
Limited Availability
£10.25
-
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

Model Alliance ML72107
1:72
BAe Sea Harrier FRS.2/FA.2 (7) ZE696 899NAS Yeovilton 1994; ZH796/715 899NAS 1994 with Sqn tail markings; XZ439 small shark mouth Eglin Air Force Base USA; ZA175/126 800NAS; ZG799/004 801NAS all m/s/grey. ZA195 H.M.S. Ark Royal 1988; ZD811 899NAS 1994; both dark sea grey.
Aircraft decals (military)
Limited Availability
£10.25
-
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

Model Alliance ML72116
1:72
BAe Hawk T.1 Stencils. Four sets for use on red/white, green/grey camo, overall grey and overall black aircraft
Aircraft decals (military)
Limited Availability
£7.70
-
In Stock
9

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Model Alliance ML72117
1:72
Sepecat Jaguar GR.1 Desert Storm (12) XX733 'Baggers'; XZ364 'Badman'; XZ108 'Rule Brittania'; XZ375 'The Guardian Reader'; XZ118 'Buster Gonads'; Xz118 'Katrina Jane'; XX358 'Mary Rose'; XZ358 'Diplomatic Service'; XX725 'Johnny Fartpants'; XX725 'Debbie or White Rose'; XX748; XX725 'Fat Slags'. All in Desert Pink with mission markings
Aircraft decals (military)
Limited Availability
£10.25
-
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

Almark AKA10
RAF Type A National Insignia/Roundels 1938-45. 25" to 84" (RAF roundels)
Aircraft decals (military)
Limited Availability
£2.99
-
In Stock
20+

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

Almark AKA28
French WWI National Insignia/Roundels various sizes
Aircraft decals (military)
Limited Availability
£2.99
-
In Stock
20+

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

Almark AKA4809
1:48
Northrop NF-5A Royal Netherlands Air Force. 314 Sqn Display aircraft and 'Double Dutch' synchro pair K-3012 K-3014 K-3042 K-3054 K-3072
Aircraft decals (military)
Limited Availability
£3.99
-
In Stock
20+

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

Almark AKC07
Junkers Ju-87B/R 'Stuka'. (5) inc. 7/St.G51; 6/St.G1; 96 Gruppo Italian Air Force
Aircraft decals (military)
Limited Availability
£2.99
-
In Stock
20+

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

Almark AKS09
1:72
Supermarine Seafire Mk.XV 3 Royal Canadian Navy and 5 Royal Navy
Aircraft decals (military)
Limited Availability
£2.99
-
In Stock
20+

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

Almark AKS11
1:72
Panavia Tornado GR.1 ZD742/CZ 17(F) Sqn 75th Anniversary scheme; ZD792/CF 17(F) Sqn
Aircraft decals (military)
Limited Availability
£2.99
-
In Stock
20+

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 KORD72144
1:72
Petlyakov Pe-2 Decals Finnish Air Force (designed to be used with Unimodel kits)
Aircraft decals (military)
Limited Availability
£8.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 KORS7298
1:72
Fokker C.VD/C.VE Finnish Weapons set (designed to be used with Broplan kits)
Aircraft detailing sets (resin)
Limited Availability
£8.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

Profimodeller PF32072P
1:32
Junkers Ju-88 Wing bomb rack Ju88 + loading system - photoetched parts - resin parts - stainless rope (designed to be used with Revell kits) [Ju-88A-1 Ju-88A-4]
Aircraft detailing sets (etched and resin)
Limited Availability
£22.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

Anigrand Craftswork ANIG4068
1:144
Blohm-und-Voss Bv-238V-1. Includes BONUS kits of the Focke-Wulf P.III, FFG.227, Junkers SPF and Focke-Achgelis Fa-225. The largest aircraft of the Axis powers. In 1940, Blohm-und-Voss began design work on a 8-engine flying boat, the Bv.P.200 for RLM, which was expected to carry 120 passengers. However, since the Jumo diesel engines never achieved operational maturity, the P.200 project was dropped. In 1941, Blohm-und-Voss subm...
Aircraft kits (resin)
Limited Availability
£117.99
-
In Stock
2

[1]   «      262   |   263   |   264   |   265   |   266      »      [269]
-100 -50 -20 -10

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