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

de Havilland Mosquito Mk.VI (5) France TE605 No 31; RAAF NA-E No 1 Sqn; Norway HP910 KK-L No 333 Sqn; F.A.A. TE711 FD-4L 811 Sqn; PRXVI USAAF NS594/U; RAF NS502/M 544 Photo Reconnaissance Sqn
Aircraft decals (military)
Limited Availability
£7.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

On Target Lockheed F-104 Starfighters (8) Lockheed F-104C 70928 479TFW George Air Force Base 1964; Lockheed F-104G 104770 4th Wing C Air Force; R-699 Royal Danish Air Force; 6-164 Turkish Air Force; 21+42 JBG 36 Luftwaffe; 5-30 23 Gruppo/5 Stormo Italian Air Force; 104-01 Escadron 104 Spanish Air Force; 4301 7th/421st Tactical Combined Wing Taiwanese Air Force (BISMay22)
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

Operation Telic Stencils. Typical stencil data as applied to RAF Panavia Tornado F.3 x 2, Panavia Tornado GR.4/GR.4A x 4 and BAe Harrier GR.7 x 4. (BISMay22)
Aircraft decals (military)
Limited Availability
£13.30
-
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

1:48
North-American F-86 Canadair and CAC Sabres Part 1. (8) XB856 ; XD753 Sqn Ldr Osbourne both 66 Sqn RAF Linton-on-Ouse 1956; 19241 (designed to be used with Airfix kits) -241 421 Sqn RCAF 1954; 19632 422 Sqn RCAF 1953; 19584 BB-560 414 Sqn RCAF 1953 Camo; 23210 RO-210 1 OUT 1950; FM190111 Sqn Malaysia Air Force 1970; 1777 Pakistan Air Force 1970 (BISMay22)
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

North-American F-86 Sabres Part 2. RAAF (9) A94-974 3 Sqn Butterworth 1968; A94-942 76 Sqn Red Diamonds Aerobatic Team 1962; A94-946 ARDU Laverton 1960; A94-949 2(F) OCU Williamstown 1960's; A94-366 5(F) OCU 1960's; A94-974 77 Sqn Butterworth; A94-949 2(F) OCU; A94-954 2(F) OCU both Williamstown 1960's; A94-988 5(F) OCU (BISMay22)
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

BAC/EE Canberra Part 1. Squadron markings only. National Insignia, serials etc on ML48903 ro ML48907 (BISMay22)
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

Photo Reconnaisance Griffon Engined Supermarine Spitfire (8) RAF FR Mk.18 TZ233/T 208 Sqn Egypt 1949; TP445/H 28 Sqn Hong Kong 1950; PR Mk.19 PS852 81 Sqn Hong Kong 1951; PM574 681 Sqn Burma 1948; PM660 OI-X 2 Sqn Furstenfeldbuck 1948; Swedish Air Force No 31040/60 1Division 1955; Royal Thai Air Force PS888 1855; FR XIVe TP339/96 Royal Indian Air Force 1947 (BISMay22)
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

1:48
Wings and Waves Pt 1 (7) Supermarine Walrus Mk.I K8343.769 718 Flt H.M.S. Exeter 1937; L2236/G9U 712 Sqn H.M.S. Cumberland 1939; Fairey Swordfish Mk.I K5960 G-4K/802 823 Sqn H.M.S. Glorious 1939; Gloster Sea Gladiator N5519/G6A 802 Sqn H.M.S. Glorious 1939; Hawker Hind S1579/571 408 Flt H.M.S. Glorious; Blackburn Skua L3007 757 Sqn, Target Tug 1939; Fairey Flycatcher S1282/10 408 Flt H.M.S. Glorious. 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

1:48
Eurofighter EF-2000 Typhoon T.1 (2) ZJ802 17(F) Sqn; ZJ822 29(R) Sqn (BISMay22)
Aircraft decals (military)
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

Sepecat Jaguar GR.3 Special schemes Part 1. (2) XX117 PA 16 Sqn 90th Anniversary 2005 black tail with gold markings; XZ112 GA 54(F) Sqn 2005 yellow tail with blue rampant lion. Both RAF Coltishall (BISMay22)
Aircraft decals (military)
Limited Availability
£7.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

Sepecat Jaguar GR.3 Special schemes Part 2. (2) XX112 EA 1914-2004 6 Sqn 90 years Anniversary. This scheme was also applied to XZ367. XZ102 FP 41 Sqn 2005 with special tail markings. Both RAF Coltishall (BISMay22)
Aircraft decals (military)
Limited Availability
£7.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

White Squared Codes and Serials for BAC/EE Canberras (RAF codes/RAF code letters/RAF serial numbers) (BISMay22)
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

Black Squared Codes and Serials for BAC/EE Canberras (RAF codes/RAF code letters/RAF serial numbers) (BISMay22)
Aircraft decals (military)
Limited Availability
£9.20
-
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

Flourescent Orange Strips for BAC/EE Canberras (BISMay22)
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

Defending the Reich Part 1. (5) Messerschmitt Bf-109G-14 White 1 9/JG 28 France 1944; Messerschmitt Bf-109G-10 Black 4 yellow/white/yellow tail bands; Focke-Wulf Fw-190A-9 Red 22 6/JG 301; Focke-Wulf Fw-190D-9 Black
Aircraft decals (military)
Limited Availability
£8.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

On Target Seafires (7) Mk.IIc MB138 7-T 880NAS H.M.S. Argus; LR792/K 834NAS H.M.S. Battler East Indies Fleet; Mk.III PR256 S-146 894NAS H.M.S. Indefatigable Pacific Fleet; Mk.XV SW912 T-134 804NAS H.M.S. Theseus 1947; PR479/L 803 Sqn RCN HMCS Warrior; SR530 AA-K 883 Sqn RCN 1948; Mk.46 LA546 900/LM Station Flt, Lossiemouth 1948 (BISMay22)
Aircraft decals (military)
Limited Availability
£10.25
-
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

1:72
Wings and Waves Pt 1 (7) Supermarine Walrus 1 K8343.769 718 Flt H.M.S. Exeter 1937; L2236/G9U 712 Sqn H.M.S. Cumberland 1939; Fairey Swordfish Mk.I K5960 G-4K/802 823 Sqn H.M.S. Glorious 1939; Gloster Sea Gladiator N5519/G6A 802 Sqn H.M.S. Glorious 1939; Hawker Hind S1579/571 408 Flt H.M.S. Glorious; Blackburn Skua L3007 757 Sqn, Target Tug 1939; Fairey Flycatcher S1282/10 408 Flt H.M.S. Glorious. Double sheet (BISMay22)
Aircraft decals (military)
Limited Availability
£11.25
-
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

1:72
Wings and Waves Part 2 (6) Fairey Swordfish.1 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 1 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 (BISMay22)
Aircraft decals (military)
Limited Availability
£11.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

British Hussars (Crimean war) The charge of the light brigade against the Russian stations is one of the most tragic moments in the battle of Balaklava during the Crimean War. This kit allows you to create light cavalry of the 8th Hussars or of the Prince Albert's Own 11th Hussars, led by Lord Cardigan during the charge of the six hundred through the valley encircled by Russian infantry and artillery. The light brigade, in spi...
Diorama accessories (injection)
Limited Availability
£9.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

Leaves Palm Howea Belmoreana colour (fauna)(flora)(Eduard sold out May 4 2022)
Diorama accessories (etched)
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

Soviet Air Force S-24 rocket (designed to be used with Eduard kits) (Eduard sold out May 4 2022)
Aircraft detailing sets (resin)
Limited Availability
£7.50
-
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

GBU-11 (Eduard sold out May 4 2022)
Aircraft detailing sets (resin)
Limited Availability
£8.60
-
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

German Military Police Ranks uniform insignia WWII (Eduard sold out May 4 2022)
Figure detailing sets (etched)
Limited Availability
£4.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

Assyrian Chariots x 2
Figures (injection)
Limited Availability
£8.50
-
In Stock
23

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

Biblical Era Libyan Army
Figures (injection)
Limited Availability
£8.50
-
In Stock
40