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=8124
		 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 51 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

Model Alliance ML489021
Hawker Tempest Mk.II/F.2/F.6 Post War (4) Mk.II PR790 EG-D 16 Sqn RAF Fasburg, Germany 1946; F.2 PR533 5R-V 33 Sqn Overall Aluminium; PR772 5R-D 33 Sqn Camouflage Both RAF Kuala Lumpur 1949; F.6 NX185/O 213 Sqn RAF Deversoir, Egypt 1949
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 ML72120
1:72
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 camouflage; 23210 RO-210 1 OUT 1950; FM190111 Sqn Malaysia Air Force 1970; 1777 Pakistan Air Force 1970
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 ML729019
Sikorsky CH-124 Sea King (1) 12407 443 Sqn RCAF 60th Anniversary scheme [HC.4 HAR.3]
Aircraft decals (military)
Limited Availability
£7.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

Model Alliance ML48164
Arctic Tigers Part 2 NATO Tiger Meet 2007 (3) Norwegian Air Force Lockheed-Martin General-Dynamics F-16B 338 Sqd; Spanish Air Force EF/A-18A 15-02 ALA 15 Zaragoza; Turkish Air Force Lockheed-Martin F-16C 192 Filo Red fin with Father Christmas
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

Model Alliance ML72119
Boeing CH-47 Chinook HC.2 RAF (9) ZH775 18 Sqn 2005; ZD757 Copenhagen 18 Sqn Operation Telic 2003; ZD981/NV 27 Sqn KFOR 1999; ZA704/EJ 1310 Flt `Double Exposure' Croatia 1999; ZA718/BN `The Survivor' 18 Sqn Operation Telic 2003; ZA712/E 27 Sqn RIAT 2005; All overall Green BS298; ZD880 7 Sqn 1995 UN Yugoslavia overall white; ZA712/ER 7 Sqn 1991 Gulf War overall desert pink with disruptive black camouflage; ZA6...
Aircraft decals (military)
Limited Availability
£10.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

Model Alliance ML729045
BAe Nimrod MR.2P Stencil Data (Current Grey Scheme) [BAe Nimrod MR.1 / MR.2 / MR.2P / R.1.] [HC.4 HAR.3]
Aircraft decals (military)
Limited Availability
£7.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

Model Alliance ML72142
BAC/EE Canberra Part 4. Bomber Canopy versions in Foreign Service (8) IP991 High Speed Silver; Q1791 Jet Bomber Conversion Unit Overall Lt Aircraft Grey; Q1792 6 Sqn;F1022 Dark Green/Dark Sea Grey/Lt Aircraft Grey. All Indian Air Force. 99+34 and 99+36 German Federal Republic; 01 and 02 F8 Wing Swedish Air Force 1960s with special noses in High Speed Silver
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 ML72100
Gulf War Blackburn Buccaneers 1991. Includes all 13 aircraft from Operation Granby. Sky Pirates flags, Scotch Whiskey names, nose art serials and codes as appropriate. All desert pink camouflage
Aircraft decals (military)
Limited Availability
£10.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

Model Alliance ML48118
On Target Supermarine 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
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 ML729022
White Squared Codes and Serials for BAC/EE Canberras (RAF codes/RAF code letters/RAF serial numbers)
Aircraft decals (military)
Limited Availability
£7.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 ML729014
Kaman SH-2F/G Royal New Zealand Navy Seasprites (6) NZ3441; NZ3442, NZ3443, NZ3444; NZ3601 and NZ3605. With large and small national insignia (BISMay22)
Aircraft decals (military)
Limited Availability
£7.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

Model Alliance ML729003
Eurofighter EF-2000 Typhoon T.1 (2) ZJ802 17(F) Sqn; ZJ822 29 (R) Sqn(BISMay22)
Aircraft decals (military)
Limited Availability
£7.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 ML729006
Douglas TA-4K Skyhawk (1) NZ6256 2 Squadron Ohakea New Zealand 1987. Overall Gold scheme. (BISMay22)
Aircraft decals (military)
Limited Availability
£7.50
-
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

Model Alliance ML729023
White Rounded Codes and Serials for BAC/EE Canberras (RAF codes/RAF code letters/RAF serial numbers) (BISMay22)
Aircraft decals (military)
Limited Availability
£7.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

Model Alliance ML729024
Black Squared Codes and Serials for BAC/EE Canberras (RAF codes/RAF code letters/RAF serial numbers) (BISMay22)
Aircraft decals (military)
Limited Availability
£7.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 ML729032
BAC/EE Canberra Pr.9 (1) XH135 39 Sqn RAF Marham 2006 Retirement scheme with winged bomb on fin
Aircraft decals (military)
Limited Availability
£9.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

Model Alliance ML48108
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

Model Alliance ML48111
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

Model Alliance ML48120
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

Model Alliance ML48125
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

Model Alliance ML48127
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

Model Alliance ML48133
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

Model Alliance ML48180
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+

   1   |   2   |   3      »      [3]

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