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 pt.id=1821
		 AND P.product_category_id = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 317 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

No Image
Kora KORPK72135
Rikugun Ki-93-1b Mosutakira - Anti-shipping Aircraft USUALLY é£72.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£48.65
-
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
Special Hobby SH48208
J-20/Hƒ©ja I 'Reggiane' 2000 Export Birds' USUALLY é£23.60. TEMPORARILY SAVE 1/3RD!!! This boxing covers the Re 2000 Italian WW2 fighter wearing colours of two foreign operators. The airframes which saw service with the Hungary air force fought on the Eastern Front while the Swedish ones watched over the neutrality of this nordic country. The kit contains four styrene sprues and one with clear canopy parts. The wing sprues ar...
Aircraft kits (injection)
Special Offers
£15.73
-
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

No Image
Special Hobby SH48213
V-156F Vindicator 'Aƒ©ronavale Service' USUALLY é£30.80. TEMPORARILY SAVE 1/3RD!!! In the effort to get modern military aircraft, France bought larger numbers of US machines in 1939. Among them also the V-156 Vindicator dive bombers. The French version was designated the V-156F and differed significantly from the US machines it was not fitted with the arrestor hook below the fuselage and instead carried air brakes in the win...
Aircraft kits (injection)
Special Offers
£20.53
-
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

No Image
Special Hobby SH48232
1:48
Bell AH-1Q/S Cobra 'US & Turkish Army' USUALLY é£39.60. TEMPORARILY SAVE 1/3RD!!! The AH-1G was the initial version of the iconic Cobra helicopter and had mainly been designed to provide close support to the ground troops. The US military quite quickly realised that what they also need is a weapon system capable of taking on enemy's armoured vehicles and tanks. And most urgent this need was felt in Europe where the danger of...
Aircraft kits (injection)
Special Offers
£26.40
-
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

Special Hobby SH72386
Re-released! Dassault-Mirage F.1EQ/ED USUALLY é£19.60. TEMPORARILY SAVE 1/3RD!!! The Mirage F.1 EQ/ED is back now in a limited release " due to long lasting demand by modellers. The nicely detailed EQ/ED Mirage comes on as many as seven styrene sprues, one clear parts sprue, the kit also contains etched antennae and resin cast Exocet missile and launch rack. The decal sheet offers markings for two Iraqi machines, each of th...
Aircraft kits (injection)
Special Offers
£13.07
-
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

No Image
Special Hobby SH72430
Focke-Wulf Fw-189B-0/B-1 'German Trainer' USUALLY é£17.80. TEMPORARILY SAVE 1/3RD!!! The twin-boom Focke-Wulf Fw 189, it is not just the reconnaissance Fw-189A version. Along with ground attack prototypes, also a batch of Fw 189B training aeroplanes was built. These differed from the others by having a streamlined fuselage pod housing a crew of five. Also the undercarriage and some features on the engine nacelles were of a d...
Aircraft kits (injection)
Special Offers
£11.87
-
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

No Image
Special Hobby SH72472
Messerschmitt Bf-109E 'Slovak and Rumanian Aces' USUALLY é£14.20. TEMPORARILY SAVE 1/3RD!!! In 1939, the Messerschmitt Bf-109E was arguably the very best fighter aeroplane in the world. And so, many air forces in Europe showed their interest to acquire the type to their inventory. And among the first foreign countries able to do so was Rumania, mainly because of the oil they were mining in Ploesti. On the other hand, one o...
Aircraft kits (injection)
Special Offers
£9.47
-
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

No Image
Q MEN QM01001
Chinese J-20 Kids Caricature/Cartoon Series Snap Fit. Includes Pilot Figure WAS é£15.99. NOW BEING CLEARED AT HALF PRICE!!!
Aircraft kits (injection)
Special Offers
£7.98
-
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

No Image
Q MEN QM01002
Lockheed-Martin F-35B Lightning II. Kids Caricature/Cartoon Series. Snap Fit.. Includes Pilot Figure. WAS é£15.99. NOW BEING CLEARED AT HALF PRICE!!!
Aircraft kits (injection)
Special Offers
£7.98
-
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

AZ Model AZM7588
North-American P-51B Mustang 'Dorsal Fin USAAF' USUALLY é£14.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£9.73
-
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

No Image
Hobby 2000 H2K48006
Mikoyan MIG-15 USUALLY é£29.99. TEMPORARILY SAVE 1/3RD!!! / S-102 (this is the new Bronco Models kits) Markings Russia x 2, Czechoslovakia and Poland
Aircraft kits (injection)
Special Offers
£19.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

No Image
IBG Models IBG72521
PZL/IAR P.11F Romanian fighter USUALLY é£11.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£7.87
-
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

No Image
IBG Models IBG72535
Focke-Wulf Fw-190D-13 Nordenham Production USUALLY é£22.50. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£14.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

No Image
IBG Models IBG72545
Focke-Wulf Fw-190D-9 Over Czech Territory USUALLY é£21.60. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£14.20
-
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

No Image
IBG Models IBG72558
Focke-Wulf Fw-190D-9 Prototype (LIMITED EDITION, will include additional 3D-Printed parts) USUALLY é£30.40. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£20.27
-
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

No Image
IBG Models IBG72902
Mikoyan MiG-29UB in Ukrainian Air Force USUALLY é£33.30. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£22.20
-
In Stock
17

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
ICM ICM32013
1:32
Polikarpov I-153 with Soviet Pilots (1939-1942) DISCONTINUED DECEMBER 2023 USUALLY é£49.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£32.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

No Image
ICM ICM32022
Fiat CR.42 LW with German Pilots DISCONTINUED DECEMBER 2023 USUALLY é£53.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£35.87
-
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

ICM ICM32032
Kokusai Ki-86a/K9W1 "Cypress", WWII Japan Training Aircraft DISCONTINUED DECEMBER 2023 USUALLY é£43.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£29.20
-
In Stock
6

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
ICM ICM32050
1:32
Stearman PT-17/N2S-3 Kaydet American Training Aircraft (100% new molds) USUALLY é£47.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£31.87
-
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

No Image
ICM ICM32061
Bell AH-1G Cobra (late production), US Attack Helicopter See ICM3001 paint set for this kit. USUALLY é£69.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£46.54
-
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

ICM ICM48065
Supermarine Spitfire Mk.VIII Pointed rudder, U.S. Air Force decals USUALLY é£14.40. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£9.60
-
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

ICM ICM48101
Mistel S1 German composite training aircraft. USUALLY é£55.80. NOW BEING CLEARED!! SAVE 1/3RD!!! Insufficient attention to long-range bombers forced the Luftwaffe command in the middle of World War II to look for other ways of striking long-range targets. One of these options was the Mistel strike composite aviation complex. The idea was to make one of the aircraft of the complex a kind of large guided projectile, the second ...
Aircraft kits (injection)
Special Offers
£37.20
-
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

ICM ICM48186
Beech C18S "Magic by Moonlight", USUALLY é£33.80. NOW BEING CLEARED!! SAVE 1/3RD!!! Airshow Aircraft One of the most successful and famous aircraft in the world aviation history was the Beech Model 18 nicknamed Twin Beech. It was designed as light twin-engine 6-8-seat light passenger aircraft. The maiden flight was in January 1937. With the entry of the US into World War II Army Air Force began to feel the need in a light mil...
Aircraft kits (injection)
Special Offers
£22.53
-
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
ICM ICM48226
1:48
Gotha Go-242A WWII German Landing Glider USUALLY é£49.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£33.20
-
In Stock
20+

   1   |   2   |   3   |   4   |   5      »      [13]
| +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