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 113

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

Planet Models PLA20172
Dornier Do-31E
Aircraft kits (resin)
Limited Availability
£89.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

No Image
Avalon AVD3002
Bell AH-1G & TH-1G (for ICM) 1. Bell AH-1G, c/n 16440, US Army Artic Programme, Elmendorf AFB, Anchorage, 1975 2. Bell AH-1G, c/n 15046, 46H, US Marines training unit, 1969 3. Bell TH-1G, c/n 34270, 70A, US Army 4. Bell AH-1G, c/n 15259, Cobra New Equipment Transition Team (NETT), Vietnam 1967 5. Bell AH-1G, c/n 17054, C/2/20 Aerial Rocket Artillery (ARA), Vietnam 1971 6. Bell TH-1G, c/n 15115, US Army
Aircraft decals (military)
Limited Availability
£10.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

No Image
Avalon AVD4012
de Havilland Chipmunk T.10 (6x paint schemes) WP972 BD-A of LAS mid-1950, WP903 of The Queen's Flt. 1968, WD384/O 1962, WB615/E of AAC 1979, WG332/G-34 of No.5 FTS Rhodesia 1952, G-BCPU (WP973) Wycombe Air Park 2014
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD4013
Gloster Gladiator Mk.I/Mk.II (6 x camouflage schemes) Mk.I: RN-S of No.72 Sq. September 1938, K7960/P of No.3 Sq. August 1938; Mk.II: N2288/GO-A of No.94 Sq. June 1940, N2309/B of No.1401(Met) Flt. 1942, KW-L of No.615 Sq. early 1940, 1339 'Pinky' of No.3 Sq. SAAF 1941
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD4014
Messerschmitt Bf-109E-1 'MG-armed Emils' 1. Bf-109E-1, Red 10, 2./JGr 101, Fƒ¼rstenwalde, Germany, August 1939 2. Bf-109E-1, White 7, 1./JG 27, France, August 1940 3. Bf-109E-1, Yellow 12, 6./JG 26, Germany, summer 1939 4. Bf-109E-1, Brown 2, 6./JG 54, France, autumn 1940 5. Bf-109E-1, White 5, 7./JG 2, summer 1940 6. Bf-109E-1, Black chevron, pilot Obstlt. Carl Schumacher, Kommodore of JG 1, December 1939
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD4015
Messerschmitt Bf-109F 'Barbarossa' 1. Bf-109F-2, W.Nr. 8326, pilot Maj. G. von Maltzahn, JG 53, Eastern Front, August 1941 2. Bf-109F, Yellow 6, 7./JG 54, Eastern Front, 1941 3. Bf-109F-4, W.Nr. 8334, White 1, pilot Oblt. W. Huy, 7./JG 77, Eastern Front, July 1941 4. Bf-109F, W.Nr. 6702, White 2, pilot Ofw. H. Neuhoff, 7./JG 53, Eastern Front, 1941 5. Bf-109F-2, W.Nr. 7079, <<-+-, pilot Maj. H. Trƒ¼benbach, JG 52, Easte...
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD4017
North-American Harvard Mk.II 1. Harvard Mk.II, FT392/G, No.1340 Flt, Kenya, Operation Mau Mau, 1953 2. Harvard Mk.II, FT442/DF, No.5 (P)AFU, Tern Hill, pilot Sgt. J. Sofranko, November 1944 3. Harvard Mk.II, AJ733/41, No.34 SFTS, Medicine Hat, 1943 4. Harvard Mk.II, 7470, No.62 Air School, Bloemspruit, South Africa, 1945 5. Harvard Mk.II, 2564/D, No.4 SFTS, Saskatoon, Saskatchewan, Canada, 1941
Aircraft decals (military)
Limited Availability
£10.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

No Image
Avalon AVD4019
Luftwaffe Fighter Unit Emblems " Jagdwaffe Pt.1
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD4020
Luftwaffe Fighter Unit Emblems " Jagdwaffe Pt.2
Aircraft decals (military)
Limited Availability
£10.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

Avalon AVD7016
1:72
Hawker Hurricane National Insignia Roundel Markings
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD7021
L-39 over Czechoslovakia 1. Aero L-39ZA, 2430, Czechoslovak Air Force 2. Aero L-39ZA, 2341, Czechoslovak Air Force 3. Aero L-39C, 0444, Czechoslovak Air Force 4. Aero L-39ZO prototype, OK-HXA/336, Czechoslovakia, Paris Air Show 1977 5. Aero L-39C, 0115, Czechoslovak Air Force 6. Aero L-139, 5501/357, Czechoslovak Air Force & Paris Air Show 1999
Aircraft decals (military)
Limited Availability
£10.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

No Image
Avalon AVD7023
Gloster Gladiator Mk.I/II 1. Gladiator Mk.I, RN-S, No.72 Sq., RAF, September 1938 2. Gladiator Mk.II, N2288, GO-A, No.94 Sq., RAF, June 1940 3. Gladiator Mk.II, N2309, B, No.1401(Met) Flt., RAF, 1942 4. Gladiator Mk.II, 1339/Pinky, No.3 Sq., SAAF, 1941 5. Gladiator Mk.I, K7960, P, No.3 Sq., RAF, August 1938 6. Gladiator Mk.II, KW-L, No.615 Sq., RAF, early 1940
Aircraft decals (military)
Limited Availability
£10.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

No Image
Avalon AVD7030
Bucker Bucker Bu-181 (6 x camouflage schemes) GL+SU of FFS A/B 115, SF+WR of FFS A/B 23, NF+IR, GK+SI of FFS A/B 43, SF+WR of FFS A/2, SK+WR of FFS A/B 4
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD7037
1:72
Luftwaffe Fighter Unit Emblems " Jagdwaffe Pt.1
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD7038
Luftwaffe Fighter Unit Emblems " Jagdwaffe Pt.2
Aircraft decals (military)
Limited Availability
£10.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
Avalon AVD7041
Supermarine Spitfire Mk.IIA 1. Spitfire Mk.IIA, P8267, JU-V, pilot Sgt. L. Zadrobilek, No.111 Sq. RAF, June 1941 2. Spitfire Mk.IIA, P8132, SO-D, pilot Sgt. M. Liskutin, No.145 Sq. RAF, autumn 1941 3. Spitfire Mk.IIA, P7366, QV-I, No.19 Sq. RAF, Matlask, September 1941 4. Spitfire Mk.IIA, P7296, JZ-22, No.57 OTU RAF, pilot W/O L. Srom, 1942 5. Spitfire Mk.IIA, P8048, N, pilot G/Cpt. K. Mrazek, RAF Northolt Station Fligh...
Aircraft decals (military)
Limited Availability
£10.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

Avalon AVD7303
LET Z-37 Cmelak (Humblebee) canopy mask (designed to be used with Kovozavody Prostejov kits)
Aircraft paint masks (self adhesive)
Limited Availability
£5.60
-
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

Eduard ED32256
Curtiss Kittyhawk Mk.I/Mk.III landing flaps (designed to be used with Hasegawa kits)
Aircraft detailing sets (etched)
Limited Availability
£19.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
Hobby 2000 H2K80009
Precision Masking Tape 5mm x 18m
Paint masking material
Limited Availability
£2.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

SBS Model SBSK7020
1:72
Fiat G.50 BIS 'German & Croatian Air Force'
Aircraft kits (resin)
Limited Availability
£35.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

ROP o.s. MNFD48047
1:48
Polikarpov PO-2/U-2 - Soviet the PO-2 at War Part 3
Aircraft decals (military)
Limited Availability
£5.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

ROP o.s. MNFDL72019
1:72
Polikarpov I-153 - Finnish captured I-153 Part 4
Aircraft decals (military)
Limited Availability
£7.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

ROP o.s. MNFDL72033
1:72
Boulton-Paul Defiant - Night predators of the RAF Part 4
Aircraft decals (military)
Limited Availability
£5.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

Airfix AX03001V
de Havilland Heron Mk.II 'Vintage Classics series'
Aircraft kits (injection)
Limited Availability
£14.49
-
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

Barracuda Studios BCR32016
(Ltd) North-American P-51D Mustang Corrected Paper Drop tanks (designed to be used with Tamiya kits) Super-detailed, seamless nose and tailcones for Tamiya's 108 gallon drop tanks capture the rough, scalloped look of the paper mache. The paper drop tanks in the Tamiya Mustang kit are accurate in shape and size, but they do not capture the 'look' of the end caps on the real tanks. The real tanks are made of compressed, impregna...
Aircraft detailing sets (resin)
Limited Availability
£10.99
-
In Stock
6

[1]   «      209   |   210   |   211   |   212   |   213      »      [269]
-100 -50 -20 -10 | +10 +20 +50

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