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

Mk-82 Bomb (2 pcs) WAS é£9.70. NOW BEING CLEARED!! SAVE 1/3RD!!! One of the most commonly used type of modern American bombs, the 500lb Mk-82, saw the service for the very first time during the war in Vietnam. It is of LDGP Low Drag, General Purpose type. The Mk-82 bomb is still used today and also serves as a basis for many modernised guided-bomb types. By assembling the parts of our set, the modeller gets two bomb units an...
Aircraft detailing sets (resin)
Special Offers
£6.47
-
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

U.S. General Purpose M43 H.E. Bombs 500 lbs (2 pcs), USA, WWII and Korean War WAS é£9.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£6.66
-
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

1:72
Allison 1710-33 (series C) WWII US. Aircraft engine. This set contains detailed model of the Allison V1710, US aircraft engine that was used during WWII in the Curtiss P-40B/P-40C and also in the prototype of the P-38. WAS é£8.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft engines and propellers (resin)
Special Offers
£5.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
1:72
Ilyushin Il-2 Control Surfaces (designed to be used with Tamiya kits) WAS é£8.30. NOW BEING CLEARED!! SAVE 1/3RD!!! Nicely detailed resin cast control surface, ie. ailerons, hotizontal stabilizator with elevators and the rudder, all of which can be shown in deployed position on yur model. Made for the Tamiya model of the Soviet iconic Il-2 ground attack plane
Aircraft detailing sets (resin)
Special Offers
£5.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

MAN LE 10.220 Bundeswehr Military Aircraft Tug + Towbar, all resin kit WAS é£74.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (resin)
Special Offers
£49.47
-
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
Soviet T-34/85 Solid Track Wheels Conversion Set (designed to be used with Tamiya kits) WAS é£19.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (resin)
Special Offers
£13.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

Gold Weathering Pigments. Stardust Metallic series WAS é£4.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Weathering products
Special Offers
£2.87
-
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

1:48
Junkers Ju-87D-5N 'Stuka' night exhaust - type B (designed to be used with Hasegawa kits) WAS é£3.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft exhausts (resin)
Special Offers
£2.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

Grilles for Geschutzwagen Tiger Fur 17cm K72 (designed to be used with Trumpeter kits) WAS é£6.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£4.47
-
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

Bucker Bu-131A/Bu-131D wheels & paint masks transverse tread with disc cover (designed to be used with ICM and Revell 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

Colt Mk.12 20mm cannons WAS é£6.99. NOW BEING CLEARED!! SAVE 1/3RD!!! from wikipedia.... the Mk 12 was standard cannon armament on gun-armed American naval fighters and attack aircraft from the early 1950s to the early 1970s, including the F4D Skyray, F3H Demon, A-4 Skyhawk, F-8 Crusader, F-11 Tiger and early navy versions of the LTV A-7 Corsair II. International customers of the A-4 Skyhawk, F-8 Crusader, and the A-7 Corsa...
Aircraft guns (resin)
Special Offers
£4.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

Messerschmitt Me-262A-2 Cockpit Set (designed to be used with Trumpeter kits) WAS é£32.40. NOW BEING CLEARED! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£21.59
-
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

No Image
Renault 4P WAS é£7.99. NOW BEING CLEARED!! SAVE 1/3RD!!! Applications ANF Les Mureaux 160T Aubert Cigale Bodiansky 20 Boisavia Mercurey Caudron C.193 Caudron C.232 Caudron C.270 Luciole Caudron C.280 Phalene Caudron C.362 Caudron C.430 Rafale Caudron C.480 Frƒ©gate Caudron C.510 Pelican Caudron C.530 Rafale Caudron C.580 Caudron C.600 Aiglon Caudron C.601 Aiglon Senior Caudron C.610 Farman F.230 Farman F.2...
Aircraft engines and propellers (resin)
Special Offers
£5.33
-
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

334 (Dutch) Squadron Pt 1. Douglas C-47B Dakota choice of ZU-3; ZU-8; ZU-11; ZU-16; X-2; X-4; X-7; X-8; X-10; de-Havilland-Canada DHC-2 Beaver S-1; S-5; S-7; WAS é£13.48. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£8.98
-
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

88mm. L/71. Sd.Kfz.164 'Nashorn, Pak-43/41 (finished June 2022*) WAS é£8.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle gun barrels
Special Offers
£5.54
-
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

7,62cm. Flak 36(R), Marder IID and III (finished June 2022*) WAS é£7.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle gun barrels
Special Offers
£5.33
-
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

Soviet T-70 45mm (designed to be used with MiniArt kits) (finished June 2022*) WAS é£6.85. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle gun barrels
Special Offers
£4.57
-
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

Consolidated Catalina weapons service platforms WAS é£3.60. TEMPORARILY SAVE 1/3RD!!!
Diorama accessories (etched)
Special Offers
£2.40
-
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

Messerschmitt Bf-109G WHEEL SET RIBBED THREAD WHEEL SET WAS é£10.99. NOW BEING CLEARED!! SAVE 1/3RD!!! (designed be used with Academy, Arsenal Model Group, Eduard kits, Hasegawa, Hobby Boss, Pegasus Hobbies (P), Revell, Tamiya, Unimodel and Zvezda kits)[Bf-109G-6/G-2 Bf-109G-6/N Bf-109G-12 Bf-109G-6 Bf-109G-4/R-3 Bf-109G-8 Bf-109G-2 Bf-109G-14 Bf-109G-10 Bf-109G-10]
Aircraft wheels (resin)
Special Offers
£7.33
-
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

Lockheed-Martin F-22A Raptor WHEEL SET (designed to be used with Academy and Hasegawa kits) WAS é£7.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£5.33
-
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
Masks for Messerschmitt Bf-109G-6 canopy frame paint mask (designed to be used with Revell kits) WAS é£8.90. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£5.94
-
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
1:35
Soviet ISU-152 accessories set (designed to be used with Zvezda kits) WAS é£42.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (resin)
Special Offers
£28.34
-
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

1:48
Ladder for Eurofighter EF-2000A Typhoon (designed to be used with Hasegawa kits) WAS é£17.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Diorama accessories (etched)
Special Offers
£11.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

1:48
Saab A-32A 'Lansen' Sharkmouth decals (designed to be used with Tarangus kits) [Saab A-32A J-32B 'Lansen' ] WAS é£10.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£6.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

Saab J-32B/J-32D/J-32E 'Lansen' for Tarangus kits. 8 base versions, including the initial 'Lansen' Gamma marking. With the extra numerals on the sheet and on the kit sheet, all 'Lansen' s can be built. WAS é£22.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£14.99
-
In Stock
5+