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 ps.id = 956
		 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 139 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 113
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

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

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
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
Italeri IT0106
Caproni Ca.313/Ca.314 USUALLY é£29.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£19.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
Italeri IT1455
1:72
Fokker F-27 Friendship SAR USUALLY é£36.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£24.66
-
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
Kora KORPK72140
Fairey Seal Foreign Service (3x camo) USUALLY é£56.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£37.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

RS Models RSMI94004
Praga E-141 with tail skid/E-241 with tail wheel LIMITED!!! USUALLY é£22.99. NOW BEING CLEARED!! SAVE 1/3RD!!! In the mid-1930s, the Praga BH-41 was the follow-on aircraft.However, there were no engines for the construction of other aircraft, so the aircraft was modernized and the ZOD-260 diesel engine was used. The aircraft flew under the designation Praga E-141. 1. Praga E.141.1, Vojenskƒ© leteckƒ© uAili...itAº, ProstAº...
Aircraft kits (injection)
Special Offers
£15.33
-
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
Hobby Boss HB80299
ZLIN Z-42M USUALLY é£7.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£5.33
-
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 RSMI92192
Bucker Bu-131A Jungmann 5 decal variants USUALLY é£17.99. TEMPORARILY SAVE 1/3RD!!! 1. Bƒ¼cker 131A, FFS A/B 72, Schwerin/Goriss, Luftwaffe 1944 2. Bƒ¼cker 131A, 'Kƒ¶r ' /Red Hearts/ Squadron, Royal Hungarian Air Force, 1941 3. Bƒ¼cker 131A, Spanish A.F., 1937 4. Tatra T-131, Slovensky letecky sbor, Slovakia 1940 5. Bƒ¼cker 131A, Liesel Bach, Bƒ¼cker Factory Aircraft, Germany 1936 The Bƒ¼cker 131 was a biplane for aerob...
Aircraft kits (injection)
Special Offers
£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
Sova-M SVM-72021
Kawasaki KDA type 88-1 scout Japanese single-engined biplane KDA-2 (Type 88) USUALLY é£35.60. TEMPORARILY SAVE 1/3RD!!! was designed for Kawasaki by the German designer Richard Vogt in 1927. The KDA-2 aircraft took part in the Second Sino-Japanese War in Manchuria (1937-1938) and stayed in service till 1940. In total, more than 1100 airplanes have been built. This kit is VERY LIMITED. ONLY 150 OF EACH.
Aircraft kits (injection)
Special Offers
£23.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
Sova-M SVM-72022
1:72
Kawasaki KDA type 88-2 scout Japanese single-engined biplane KDA-2 (Type 88) USUALLY é£35.60. TEMPORARILY SAVE 1/3RD!!! was designed for Kawasaki by the German designer Richard Vogt in 1927. The KDA-2 aircraft took part in the Second Sino-Japanese War in Manchuria (1937-1938) and stayed in service till 1940. In total, more than 1100 airplanes have been built. This kit is VERY LIMITED. ONLY 150 OF EACH.
Aircraft kits (injection)
Special Offers
£23.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

Azur AZUR11272
PZL P.11c 'First Shots' WAS é£15.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£10.53
-
In Stock
7

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

Azur AZUR11572
PZL P.11c 'Foreign Service' WAS é£15.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£10.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
Arma Hobby AH70048
Grumman F4F-4 Wildcat WAS é£19.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£13.33
-
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
AZ Model AZM7807
Messerschmitt Bf-109E-1 'Experten 2' USUALLY é£13.70 TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£9.13
-
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
RS Models RSMI92282
Kawasaki Ki-60 USUALLY é£17.99 TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£11.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
Special Hobby SH72459
Messerschmitt Bf-109E-1 'Legion Kondor' USUALLY é£13.99 TEMPORARILY SAVE 1/3RD!!! pre WWII Between 1939-40, the Messerschmitt Bf-109E was arguably the very best fighter aeroplane of the world. The type was produced in two main subvariants differing by the type of armament, either heavily or lightly armed. The lightly armed airframe was fitted with machine guns only, two of them above the engine, the other two in the wings. The...
Aircraft kits (injection)
Special Offers
£9.33
-
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
Eduard kits EDK2134
Mikoyan MiG-21PF AND MiG-21PFM STRIBRNE SIPY Limited edition kit of of Soviet Cold War jet fighter Mikoyan MiG-21PF AND MiG-21PFM in Czechoslovak Air Force in scale. DOUBLE KIT!!! WAS é£52.40. TEMPORARILY SAVE 1/3RD!!! - plastic parts: Eduard - marking options: 14 - decals: Eduard - PE parts: pre-painted - painting mask - resin part SPECIAL LOW PRICE! LIMITED! NO MORE WHEN SOLD! (Eduard sold out March 2024 part 1)
Aircraft kits (injection)
Special Offers
£34.93
-
In Stock
20+

   1   |   2   |   3   |   4   |   5      »      [6]

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