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

Kora KORS72217
1:72
Dornier Do-17Z-1/Z-2 late engine cowlings (designed to be used with Airfix kits)
Aircraft conversions (resin)
Limited Availability
£9.55
-
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

Modeldecal MD069
1:72
Armeĩ de L'Air. North-American F-100D Super Sabre/North-American F-100F Super Sabre 3rd/11th Escadres 1960-78; Dassault-Mirage F.1B/C 5,10,12, 30 Escadres 1976-82; Aeronavale Vought F-8E(FN) Crusader 12th/14th Flotille 1964-83
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

Maestro Models MMMK4912
1:48
2 x Rocket pod m/57 for J32B 'Lansen' & J35 Draken. (To fit on Draken, Y-pylon MMMK4812 is required)
Aircraft detailing sets (resin)
Limited Availability
£11.50
-
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

AIM - Ground Equipment GE144006
1:144
Airfield radar scanner - 39.html
Diorama accessories
Limited Availability
£8.10
-
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

AIM - Ground Equipment GE144054
1:144
Avro Vulcan B.2/K.2 Tow Bar (V-Bomber support series) - For more information on this product, please click on the link to go to the Aircraft In Miniature web page. 2 (designed to be used with Anigrand Craftswork, Great Wall Hobby and Welsh Models kits)
Diorama accessories
Limited Availability
£7.65
-
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

Xtradecal X48006
USAFE Pt 2. (5) General-Dynamics EF-111A Raven 67-034/UH 42nd ECS Upper Heyford; F-111F 70-412/LN 493rd TFS Lake USAFE Part 2. (5) General-Dynamics EF-111A Raven 67-034/UH 42nd ECS Upper Heyford; F-111F 70-412/LN 493rd TFS Lakenheath; Fairchild A-10A 81-978/WR 78th TFS; 80-280/WR 91st TFS; 81-981/WR 81st TFW Wing Co Bentwaters; 81-990/AR 509th TFS Alconbury; Fairchild A-10A 81-978/WR 78th TFS; 80-280/WR 91st TFS; 81-981/WR 81s...
Aircraft decals (military)
Limited Availability
£4.99
-
In Stock
50++

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

Xtradecal X48004
1:48
Reccon Aircraft Lockheed TR-1A/B and U-2R. 6 different tail art; Navy; China; McDonnell RF-4C 68-657 'Starize' 10TRW Alconbury 1986 Euro scheme [TR-1B]
Aircraft decals (military)
Limited Availability
£4.99
-
In Stock
50++

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

Yahu Models YMA4846
Messerschmitt Bf-109B (designed to be used with Classic Airframe and Special Hobby kits)
Aircraft detailing sets (etched)
Limited Availability
£3.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

Attack Hobby Kits ATK14415/22
Mikoyan MiG-19UTI AND Shenyang JJ-6 two seat trainer
Aircraft kits (injection)
Limited Availability
£17.70
-
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

New Ware NWAM0186
1:48
Sukhoi Su-25K BASIC (designed to be used with Smer kit SM48857)
Aircraft paint masks (self adhesive)
Limited Availability
£6.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

Anigrand Craftswork ANIG4088
1:144
Shin Maywa US-1A JMSD air-sea rescue amphibian. (Includes these BONUS kits Kayaba Ka-1, Mitsubishi LR-1 and Mitsubishi MU-2S) In 1966, the JMSDF awarded Shin Maywa company a contract to develop a large STOL aircraft for anti-submarine warfare and air-sea rescue work respectively. Two prototypes were built under designation PS-X and flight tests began in October 1967, leading to an order for PS-1 ASW production. The PS-1 had no...
Aircraft kits (resin)
Limited Availability
£87.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

Noy's Miniatures NM48035
1:48
"Large Soviet PAG-14 Dispersal": This plotter-printed item depicts a significantly larger parking area for 1/48 Soviet & Warsaw Pact aircraft. It comes inside a sturdy carton tube and is shipped like a poster. Approx. dimensions: Length: 59.4 cm (23.4 inch); Width: 42 cm (16.53 inch). (designed to be used with ICM kits)[Mikoyan MiG-25RBT]
Bases and cases
Limited Availability
£32.40
-
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

Microscale SS480929
1:48
North-American P-51D Mustang (2) 415392/20 2nd ACG Lt B.Mayer 'Cheese Cake Chassis' Burma 1945 black lightning bolts on wings and fuselage; 413693 FT-I 353FS/354FG Capt Bruce Carr 'Angel's Playmate'. Both overall natural metal
Aircraft decals (military)
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

No Image
Xtracolor X367
Virgin Atlantic Grey. Must be overcoated with X368 Mica for pearlescent finish
Paint, Enamel 14ml
Limited Availability
£2.00
-
In Stock
30+

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

New Ware NWAM0138
McDonnell F-101A Voodoo EXPERT set for aircraft canopy including window seals and inner sides of windows, wheels. nose cone, bottom of the rear of the aircraft (designed to be used with VAL72094 kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.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

New Ware NWAM0140
1:72
Mask McDonnell RF-101A Voodoo ADVANCED for aircraft canopy including window seals, wheels. nose, camera windows, bottom of the rear of the aircraft (designed to be used with VAL72092 kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.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

Freightdog FDR72103
Mark 7 Nuclear bomb (for US 1950s types)
Aircraft detailing sets (resin)
Limited Availability
£5.00
-
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

Art Model ART7211
1:72
Sukhoi Su-28
Aircraft kits (injection)
Limited Availability
£26.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

Anigrand Craftswork ANIG2112
1:72
Fairchild XC-120 Packplane Modified C-119 with detachable cargo pod. In 1948, the USAF requested Fairchild to develop a new transport airplane based on the C-119 Flying Boxcar, as the cargo or troops carriers with a better and quicker way to deliver cargo, or an air-deliverable field hospital. Fairchild submitted a proposal for the conversion of the C-119B from the production line into a new designed machine. The aircraft reta...
Aircraft kits (resin)
Limited Availability
£147.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

Warbird Decals WB72037
1:72
Thunderbirds support aircraft Douglas C-54D Skymaster Circa 1959-63
Aircraft decals (military)
Limited Availability
£19.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

New Ware NWAM0134
1:48
Junkers Ju-87B-1 'Stuka' kabuki masks - CLOSED AIRCRAFT CANOPY ONLY (designed to be used with Airfix kits)
Aircraft paint masks (self adhesive)
Limited Availability
£7.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

AZ Model AZM7702
Bell AH-1G Huey Cobra (sprues only), 3 sets, no decals etc, plastic parts only... (AZ Models sold out April 2024)
Aircraft kits (injection)
Limited Availability
£17.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

Xtracolor X361
Qantas Red enamel ;;;
Paint, Enamel 14ml
Limited Availability
£2.00
-
In Stock
27

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

Miniwing-Plastic MINI306
1:144
Cessna O-1 BIRD DOG / Royal Thai Air Force
Aircraft kits (injection)
Limited Availability
£6.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

Anigrand Craftswork ANIG4023
1:144
Daimler-Benz Project-A Parasite bomber system. Also includes BONUS kits of the Blohm-und-Voss Bv.P.212, Arado Ar.E.581-4, Messerschmitt Me.P.1110 Ente and Sombold So-344. In 1943, instructed from Reichsmarschall Goring for the design and produce very long-range bombers which would be capable of attacking targets in the U.S.A. and Soviet industrial plants far beyond the Germany, Daimler-Benz combined with Focke-Wulf to form a j...
Aircraft kits (resin)
Limited Availability
£87.99
-
In Stock
2

[1]   «      253   |   254   |   255   |   256   |   257      »      [269]
-100 -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