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=1819 AND P.product_manufacturer_id=7973
		 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 34 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

Ilyushin IL-2M3 armament (designed to be used with Academy, Accurate Miniatures, Eduard kits, Italeri and Revell kits) WAS é£20.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£13.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

1:48
Supermarine Spitfire Mk.IXe armament (designed to be used with Hasegawa kits)[mk.ixc] WAS é£13.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£9.13
-
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

Supermarine Spitfire armament type A. Set contains armament boxes for guns 7,7mm (8 pcs.), detailed guns 7,7 mm, covers (designed to be used with Tamiya kits) WAS é£10.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£7.20
-
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

Hawker Sea Hawk FGA.6/Mk.100/Mk.101 control surfaces set (designed to be used with Trumpeter kits) WAS é£13.73. NOW BEING CLEARED!! UNDER HALF PRICE!!!
Aircraft detailing sets (resin)
Special Offers
£5.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

WWII RAF MC Bomb Mk.I 250lb (4 pcs) WAS é£8.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£5.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

AGM-88 HARM Air-to-Surface Missile + NATO / US LAU-118 Launcher Adaptors (2 pcs) Set contains 2 highly detailed anti-radiation air-to-surface missiles. The missiles are cast in one piece with only separate mid-body fins. The set also contains NATO launcher adaptors, LAU-118 launcher adaptors for Us aircraft, decal sheet with stencils, 20 color photo-etched Remove Before Flight tags and plain photo-etched fret with small parts.
Aircraft detailing sets (resin)
Special Offers
£11.53
-
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

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

1:48
Mk-82 AIR BSU49B Bomb (Air Inflatable Retarder) (2 pcs). WAS é£9.20.NOW BEING CLEARED!! SAVE 1/3RD!!! The Mk-82 AIR is one of the improved versions of the standard Mk-82 type. It is equipped with a BSU-49/B tail section, or so-called Air Inflatable Retarder, kind of merge of a chute and a baloon. After the bomb is dropped, the nylon-made retarder gets inflated behind the body of the bomb using the aerodynamical forces. This ...
Aircraft detailing sets (resin)
Special Offers
£6.13
-
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

Japanese Naval Bombs 250k x 2 WAS é£9.90.NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£6.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

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

WWII Luftwaffe 600L Drop Tanks (2 pc) WAS é£11.70. NOW BEING CLEARED!! SAVE 1/3RD!!! Special Hobby sold out August 2023
Aircraft detailing sets (resin)
Special Offers
£7.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

AGM-88 HARM Air-to-Surface Missile + NATO / U.S. LAU-118 Launcher Adaptors (2 pcs) (discontinued December 10th 2020) WAS é£19.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£13.20
-
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:32
Lockheed F-104G WAS é£20.50.NOW BEING CLEARED!! SAVE 1/3RD!!! Interior set European version with C 2 seat. Set contains completely new cockpit for the European F-104G (Italy, Belgium, the Netherlands, Greece, Spain,). Not suitable for Japanese, U.S. and Canadian machines. The set contains cockpit tube, ejector seat, instrument panel, side consoles, bulkheads and other smal parts. The set also contains color photo- etched part...
Aircraft detailing sets (resin)
Special Offers
£13.67
-
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

Lockheed F-104G/S Starfighter Electronics and Ammo Boxes Set WAS é£18.20.NOW BEING CLEARED!! SAVE 1/3RD!!! 1/32 for Italeri kit The set contains brand new electronics and ammo boxes that were placed in the fuselage behind the cockpit. Beside the boxes, there are also their respective covers contained in the set. It is intended for one of the most known and wide-spread U.S. jets that was used not only in the US, but also in Ca...
Aircraft detailing sets (resin)
Special Offers
£12.13
-
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

EADS GAF Telelens Pod. WAS é£22.80. BEING CLEARED!! SAVE 1/3RD!!! EADS GAF Telelens Recce Pod is a modern optical reconnaissance pod for mid and high flight levels and can be fitted to a model of West-European Panavia Tornado aircraft in service of German Luftwaffe.The set contains a decal sheet with stencils and a detailed guide showing their correct position.
Aircraft detailing sets (resin)
Special Offers
£14.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

Mk-82 Bomb (2 pcs) WAS é£13.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
£8.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

Mitsubishi A6M5c Zero Tail Cone Set (designed to be used with Hasegawa kits) WAS é£8.99. NOW BEING CLEARED!! SAVE 1/3RD!!! The set contains components enabling the modeller to remove the fairings above the tailwheel (fuselage bulkhead, new tailwheel, tailwheel strut, tail cone fairing panels) and show the tailwheel area including the fuselage bulkhead. Japan / WWII
Aircraft detailing sets (resin)
Special Offers
£5.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

1:72
North-American B-25J Mitchell exterior (designed to be used with Revell kits) (finished 9/22) WAS é£11.50. NOW BEING CLEARED!! SAVE 1/3RD!!! Special Hobby sold out August 2023
Aircraft detailing sets (resin)
Special Offers
£7.67
-
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

Martin B-26 Marauder armament set (designed to be used with Eduard kits and Hasegawa kits) [B-26B B-26C B-26F B-26G] (Special Hobby sold out June 2023) WAS é£12.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£8.13
-
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

Mitsubishi A6M2/A6M3/A6M5 Zero - Exterior set set (designed to be used with Hasegawa kits) Highly detailed resin set containing tail cone parts for the most famous WWII Imperial Japanese Navy fighter. The set contains tail wheels and strut with its mount, last fuselage bulkhead and tail cone. It can be built with tail cone removed. This set is intended for A6M2/ 3/ 5 of all subversions produced by Hasegawa. WAS é£6.30. NOW B...
Aircraft detailing sets (resin)
Special Offers
£4.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

1:72
Letov S-328 Control surfaces set (designed to be used with Special Hobby kits) WAS é£14.20. NOW BEING CLEARED!! SAVE 1/3RD!!! (finished February 2023)
Aircraft detailing sets (resin)
Special Offers
£9.47
-
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

Folland Gnat F.1 Control surfaces (designed to be used with Special Hobby kits) WAS é£11.50. NOW BEING CLEARED!! SAVE 1/3RD!!! [Ajeet Mk.I] (finished February 2023)
Aircraft detailing sets (resin)
Special Offers
£7.67
-
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

Mk-82 Bomb (2 pcs) WAS é£9.20. 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 tody and also serves as a basis for many modernised guided-bomb types. (finished 10-10-2022) By assembling the parts of our set, the modeller g...
Aircraft detailing sets (resin)
Special Offers
£6.13
-
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
Boeing F-18E/F Hornet Gun Bay (designed to be used with Academy kits) USUALLY é£6.60. TEMPORARILY SAVE 1/3RD!!! The set offers new, open rotary cannon and ammo drum compartment for the new F-18E/F kits made by Academy.
Aircraft detailing sets (resin)
Special Offers
£7.07
-
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+