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 ps.id = 956
		 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 2,250 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

Planet Models PLA11872
Bolchovitinov Typ S-1 'single engine' ski plane (finished February 2023)
Aircraft kits (resin)
Limited Availability
£41.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 ED72506
BAC/EE Canberra PR.9 ladder (designed to be used with Airfix kits)(Eduard sold out February 2023)
Diorama accessories (etched)
Limited Availability
£7.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

Eduard ED73018
Swedish Air Force Remove Before Flight yellow/black (designed to be used with Airfix, Hasegawa, Heller, Special Hobby and Tarangus kits) [Saab J-29A / J-29B Tunnan J-32B/E 'Lansen' JA-37 SF-37 'Viggen' etc etc](Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£9.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

Eduard ED73580
1:72
Sukhoi Su-33 Flanker D (designed to be used with Zvezda kits)(Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£16.30
-
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
Eduard ED73717
North-American P-51B/P-51C Mustang (designed to be used with Hasegawa and Hobby 2000 kits)(Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£11.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
Eduard ED73737
Let Z-37A Weekend (designed to be used with Eduard kits)(Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£14.40
-
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
Eduard EDD72017
Focke-Wulf Fw-190A-5 national insignia (designed to be used with Eduard kits) (Eduard sold out February 2023)
Aircraft decals (military)
Limited Availability
£7.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

Eduard EDSS119
Messerschmitt Bf-109G (designed to be used with Italeri kits) Includes about 20 of the more important and prominent parts for detailing the aircraft. Most sets are also available as part of a double set for the modeller who wants even more detail. Please see the picture for the double set part number. (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£2.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

Eduard EDSS588
Supermarine Spitfire Mk.IIa (designed to be used with Revell kits) Includes about 20 of the more important and prominent parts for detailing the aircraft. Most sets are also available as part of a double set for the modeller who wants even more detail. Please see the picture for the double set part number. (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£9.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

Eduard EDSS686
Messerschmitt Bf-109F-2 (designed to be used with Revell kits) Includes about 20 of the more important and prominent parts for detailing the aircraft. Most sets are also available as part of a double set for the modeller who wants even more detail. Please see the picture for the double set part number. (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£9.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

RS Models RSMI92185
Avia B-534/I version. The prototype of B-534 flew for the first time on 25 May 1933. The first procurement order for the B-534 was issued from the Ministry of National Defence to the Avia Company on 17 July 1934 and was for 147 aircraft's. The first machines were delivered to the Air Force in autumn of 1935. The Ministry of National Defence ordered the next, second procurement batch of 46 B-534's in 1936. The first 26 belong...
Aircraft kits (injection)
Limited Availability
£19.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

Anigrand Craftswork ANIG2031
Douglas XB-42 Mix-Master. In 1943, the Douglas began a company funded study to a high speed bomber design which was not liked the traditional wing mounting engines. It was an unusual idea to install the two engines within the fuselage driving a pair of propellers mounted at the tail. That can leaving the wing and fuselage clean and free of aerodynamics-reducing protrusions. Douglas submitted an unsolicited proposal to U.S.A.A....
Aircraft kits (resin)
Limited Availability
£57.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

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

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

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. 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+

[1]   «      69   |   70   |   71   |   72   |   73      »      [90]
-50 -20 -10 | +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