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 = 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 2,274 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

No Image
K-type Blimp (K-19/43/125) 'Special Markings' USUALLY é£19.70. TEMPORARILY SAVE 1/3RD!!! Non-rigid airships (or blimps) were the first types of lighter-than-air craft. Since the turn of the 20th century, they have been employed on a large scale by armed forces and also engaged commercially. The Goodyear Aircraft Company, the foremost producer of airships in the United States, produced a series of K-class airships which became...
Aircraft kits (injection)
Special Offers
£12.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
McDonnell F-4E Phantom 338 Sqn ARES callsign PIOU... WAS é£17.30. TEMPORARILY SAVE 1/3RD!!! the name is taken from a person in a famous commercial on Greek television and was the paint for the squadrons 60 years anniversary. Also on the same plane was painted the 66 years squadrons anniversary this was the same time that plane ended it's operational life.
Aircraft decals (military)
Special Offers
£11.54
-
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
Special offer! 2 for less than the price of PF32398 JB-2 Loon. Resin kit with etched parts and decals and PF32399 Transport buggy for the JB-2 Loon. (designed to be used with Profimodeller kits)
Aircraft kits (resin)
Special Offers
£79.98
-
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

Schwerer Panzerspahwagen Kommandwagen, antenna etc WAS é£49.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£33.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

Schwere Spahwagen (heavy armoured railway wagon) WAS é£49.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£33.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
Pz.Kpfw.V Ausf.D Panther "Stadtgas" Fuel Tanks Sd.Kfz.171 Type Liquefied Petroleum Gas WAS é£69.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£46.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

Pz.Kpfw.V Ausf.G Panther With Steel Road Wheels WAS é£26.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£17.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
Sukhoi Su-27S (designed to be used with Kitty Hawk Model kits) WAS é£7.40. TEMPORARILY SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£4.93
-
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
Sukhoi Su-27UB (designed to be used with Kitty Hawk Model kits) WAS é£8.60. TEMPORARILY SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£5.73
-
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
1:32
Mk-77 Mod 4 Fire Bombs with decals. We have teamed up with AOAdecals for this release. Each set will include two fire bombs along with the stencils provided by AOA. WAS é£14.99. TEMPORARILY SAVE 1/3RD!!! Some minor clean up of the forward end of the bomb will be required to remove the print support nibs. A few swipes with a file and sanding sticks does the trick. Set contains two Fire Bombs and a decal sheet
Aircraft detailing sets (resin)
Special Offers
£9.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
McDonnell F-4E Phantom 339 SQ AT RIAT 2016 The paint that 339SQN use for the participation on RIAT AIR SHOW 2016 WAS é£18.40. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£12.27
-
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

Pz.Kpfw.IV Ausf.H (designed to be used with Zvezda kits) WAS é£16.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£10.93
-
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

Mikoyan MiG-23, MiG-23P early wheel set (designed to be used with all kits) WAS é£6.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£4.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

Saab J-29C Tunnan recce conversion. WAS é£26.99. NOW BEING CLEARED!! SAVE 1/3RD!!! The S29C Tunnan was a converted J29B Tunnan fighter, fitted with five cameras that replaced the guns - so no armament was carried on this version. It could be fitted with extra fuel tanks or ECM pods. The letter S is derived from the Swedish word Spaning, translating into reconnaissance. A total of 76 aircraft were built from 1954 through 1956....
Aircraft conversions (resin)
Special Offers
£17.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

Curtiss P-40K (canopy frame paint masks (inside and outside)) insignia and markings masks (designed to be used with Hasegawa kits) WAS é£9.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£6.67
-
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

FAB-100 Soviet WWII bombs WAS é£5.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£3.66
-
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

No Image
Nakajima B6N2 wheel bay paint masks (designed to be used with Hasegawa kits) made from New Soft Oramask WAS é£6.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£4.53
-
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

Messerschmitt Bf-109E-3 (designed to be used with Tamiya TA61050 kits) WAS é£4.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£3.07
-
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

Let L-13J motorised Blanik, conv. (designed to be used with MARK I Models kits) (incl. decals) WAS é£7.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£5.20
-
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

No Image
de Havilland B-36 Mosquito in CzAF WAS é£13.20. TEMPORARILY SAVE 1/3RD!!! 1. B-36 (Mosquito FB Mk.VI), PZ247, pilot Alois ...é tanc, 2nd Flt., Air Regiment No.24, 1947 2. B-36 (Mosquito FB Mk.VI), TE603, pilot Vlastimil VeselÆ'é½, Air Regiment No.24, Ruzyn? Air Day, 7th September 1947 3. B-36 (Mosquito FB Mk.VI), MM430, Air Regiment No.25, 1950
Aircraft decals (military)
Special Offers
£8.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

No Image
Supermarine Spitfire Mk.VB of No.313 (Czechoslovak) Squadron during "Operation Starkey" WAS é£8.80. TEMPORARILY SAVE 1/3RD!!! 1. Spitfire Mk.VB, EP110, F/L O. Ku"éera, Hawkinge, September 1943 2. Spitfire Mk.VB, AA969, F/L A. Hochmal, Hawkinge, September 1943 3. Spitfire Mk.VB, BL767, P/O PÆ'épa, Hawkinge, September 1943
Aircraft decals (military)
Special Offers
£5.87
-
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
Supermarine Spitfire Mk.VB of No.313 (Czechoslovak) Sq., Sgt PavLk's cartoons, WAS é£16.70. TEMPORARILY SAVE 1/3RD!!! Pt.1 1. Spitfire Mk.VB, AB276, F/Lt V. Hajek 2. Spitfire Mk.VB, AD380, F/O A. Hochmal 3. Spitfire Mk.VB, BL973, F/L S. Fejfar 4. Spitfire Mk.VB, BM242, S/L K. Mrazek 5. Spitfire Mk.VB, BM301, F/O J. P...a"Æ'éhoda 6. Spitfire Mk.VB, W3962, F/O K. Vykoukal
Aircraft decals (military)
Special Offers
£11.14
-
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
Supermarine Spitfire Mk.VB of No.313 (Czechoslovak) Sq., Sgt PavLk's cartoons, Pt.2 WAS é£16.70. TEMPORARILY SAVE 1/3RD!!! 1. Spitfire Mk.VB, BL581, F/L K. Vykoukal 2. Spitfire Mk.VB, AA865, Sgt. V. Truhla...a" 3. Spitfire Mk.VB, P8537, Sgt. J. Hlou...é¾ek 4. Spitfire Mk.VB, P/O J. Ku"éera 5. Spitfire Mk.VB, AD384, Sgt. O. Kresta 6. Spitfire Mk.VB, BM306, F/O V. JÆ'écha
Aircraft decals (military)
Special Offers
£11.14
-
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
de Havilland Mosquito PR Mk.IX/Mk.XVI of Czechoslovak airmen WAS é£8.80. TEMPORARILY SAVE 1/3RD!!! 1. Mosquito PR Mk.IX, LR422, S/Ldr E.B. Kraus, No 540 Sq. RAF, October 1943 2. Mosquito PR Mk.IX, LR432, W/O F. Kepra + F/Lt K. Vokoun, No.544 Sq. RAF, November 1944 3. Mosquito PR Mk.XVI, NS502M, F/Lt J. Ku"éera + F/O O. Filip, No.544 Sq RAF, November 1944
Aircraft decals (military)
Special Offers
£5.87
-
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
No.313 (Czechoslovak) Sq. Supermarine Spitfires, Pt.1 WAS é£17.40. TEMPORARILY SAVE 1/3RD!!! 1. Spitfire Mk.I, X4031, F/L K. Mrazek 2. Spitfire Mk.VB, W3962, F/O K. Vykoukal 3. Spitfire Mk.VB, AA865, Sgt. V. Truhla...a" 4. Spitfire Mk.VB, BL973, F/L S. Fejfar 5. Spitfire Mk.VB, AD380, F/O A. Hochmal 6. Spitfire Mk.VB, P8537, Sgt. J. Hlou...é¾ek 7. Spitfire Mk.VB, BM301, F/O J. P...a"Æ'éhoda 8. Spitfire Mk.VB, AA969, F...
Aircraft decals (military)
Special Offers
£11.60
-
In Stock
5+