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_manufacturer_id=7973 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		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
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 1,007 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
1:72
Junkers Ju-88A/C Life Boat 1/72 All versions of the Junkers Ju 88 twin engine bomber had the life boat fitted in the rear fuselage, ahead of the empennage, beneath a rectangular panel. Our resin cast set offers the raft compartment, the raft itself and the cover panel, too. Fits all versions of the Revell's Ju 88. (designed to be used with Revell kits)
Aircraft detailing sets (resin)
Catalogue
£6.50
-
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
German Speed Boat Life Raft / Schnellboot Rettungs Floss. This life raft set consists of resin parts accompanied by a fret of photo etches. The raft itself offers much more detail than those styrene ones from Revell's E-Boot and Schnellboot kits and is a rather straightforward replacement. The German fast boats were equipped with a special style of the life raft. (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£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

No Image
German Kriegsmarine Life Raft / Rettungs Floss. Set of a life raft, resin parts come along with a fret of etches. The raft was usually used on various Kriegsmarine vessels, from the smallest kutters to the heaviest battleships (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£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

No Image
German Infantryman Carried on Hetzer 1944/45 Highly realistic figure of a tired German infantryman sitting on the top armour plate of the Hetzer tank destroyer. His personal eqipment and dress is typical for late war years 1944-45. (designed to be used with Italeri and Tamiya kits)
Figures (resin)
Catalogue
£11.99
-
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
1:72
US Navy Grumman F-6F Hellcat Pilot and Mechanic The set offers a figure of a navy pilot standing beside his Hellcat (it is also okay to place him next to another type, F6F-3 F6F-4 F4F-5/F4F-3, F4U etc..). His mechanic crouches nearby ready to remove the wheel chocks. (designed to be used with Airfix, Eduard Hasegawa etc kits)
Figures (resin)
Catalogue
£7.40
-
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
Hawker Hurricane Mk.I Control Surface The set contains new resin control surfaces ailerons, tail fin with rudder and flaps (designed to be used with Arma Hobby kits)
Aircraft detailing sets (resin)
Catalogue
£7.40
-
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
Hawker Hurricane Mk.I - Reserve Fuel Tank. Resin cast fuel tank which come along with the removed cover panel. The tank is located in the fuselage behind the engine and right in front of the cockpit. Will definitely make your model more life like, and the assembly is easy and straight forward (designed to be used with Arma Hobby kits)
Aircraft detailing sets (resin)
Catalogue
£7.40
-
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
Bristol Beaufighter Mk.21 - Hedgehog Flame Damper Exhausts / Aussie Type Hedgehog style night exhausts for Beaufighters of Australian provenance. Designed to fit Airfix kits, which unfortunately lack this important detail. (designed to be used with Airfix kits)
Aircraft detailing sets (resin)
Catalogue
£3.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

No Image
1:72
Bristol Beaufighter - Hedgehog Flame Damper Exhausts / British Type Hedgehog exhausts for British-manufactured Beaufighters. They bring much more realism than the styrene items found in the kit (designed to be used with Airfix kits)
Aircraft detailing sets (resin)
Catalogue
£3.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

Nakajima Ki-43-II Hayabusa Control Surface Set (designed to be used with Special Hobby kits)
Aircraft detailing sets (resin)
Catalogue
£7.40
-
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
Douglas A-26B Invader Cockpit (designed to be used with ICM kits) Detail resin cast cockpit with photoetches [A-26B-15, B-26C-50]
Aircraft detailing sets (resin)
Catalogue
£19.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

No Image
ESK 2000 B German WWII Gun Camera. The German camera gun could be mounted on either the wing or fuselage of the Messerschmitt Bf-109and other single engine fighter planes and was used for combat training. Some other types, as the Bf-110, could carry the gun inside the nose.
Aircraft detailing sets (resin)
Catalogue
£7.40
-
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
Messerschmitt Bf-109E-1/5 Wing Machine Guns The wing armament of the light weight Bf-109 E-1 and E-5 versions consisted of a pair of 7.9mm machine guns. This set offers open wing gun compartments with machine gun bodies and also contains a few photo etched parts. Designed to fit the Bf-109 E-1/5 kits from Special Hobby and those reboxed by Eduard. (designed to be used with Special Hobby kits)
Aircraft guns (resin)
Catalogue
£7.40
-
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
Bristol Beaufighter Mk.21 - Hedgehog Flame Damper Exhausts / Aussie Type Hedgehog exhausts with realistic detail. These exhausts replace directly those found in the Revell kit. (designed to be used with Revell kits)
Aircraft exhausts (resin)
Catalogue
£6.50
-
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
Messerschmitt Bf-109E Detailed Propeller Spinner. The variable pitch propellers of the Bf-109 fighters required regular maintenance, which is why so many photographs of the Bf-109 while being serviced show the planes with their spinners removed. And this very situation can be now recreated by the use of our set which contains nicely detailed propeller spinner and new, separately cast blades. (designed to be used with Special H...
Aircraft detailing sets (resin)
Catalogue
£4.30
-
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
Messerschmitt Bf-109E Tailwheel with Strengthened Leg. To make sure that the tail wheel of your Bf-109E kit will not break under the added weight of all the detail resin sets you have put into your model, we offer you a new leg made of special, tough, oven-baked resin material. The leg comes with the tail wheels and is a direct replacement for the styrene item of Special Hobby's and Eduard's Bf-109Es. (designed to be used with...
Aircraft wheels (resin)
Catalogue
£1.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
ESK 2000 B German WWII Gun Camera. The German camera gun could be mounted on either the wing or fuselage of the Messerschmitt Bf-109and other single engine fighter planes and was used for combat training. Some other types, as the Bf-110, could carry the gun inside the nose.
Aircraft detailing sets (resin)
Catalogue
£6.50
-
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
DAP Beaufighter Mk.21 Conversion Set. This conversion set brings all components needed to use the Airfix Mk.X kit and finish it in the Mk.21 variety which was licence-built in Australia for the RAAF. The set contains resin conversion parts, vacuum-formed canopy of the observer and a sheet of decals with markings for two Aussie options. The first one was adorned with a cartoon on the nose, the other carries it on its tail rudde...
Aircraft conversions (resin)
Catalogue
£11.50
-
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
1:72
Messerschmitt Bf-109E-3/Bf-109E-4/Bf-109E-7 Wing Guns (designed to be used with Special Hobby kits) The wing gun barrels could be accessed via open leading edge panels as shown in this set. It also contains the open ammo boxes for the MG FF cannon. Designed for the new and superb Special Hobby Bf-109E
Aircraft guns (resin)
Catalogue
£6.50
-
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
Messerschmitt Bf-109E-3/Bf-109E-4/Bf-109E-7 FuG VII Radio Equipment (designed to be used with Special Hobby kits) Finely detailed set of the FuG VII wireless. They were located inside the fuselage behind the pilot's cockpit. The set contains the radios as well as a section of the fuselage internal structure. Some of the parts also come on the fret of photo etches. Designed for the new and superb Special Hobby's Bf-109E kit
Aircraft detailing sets (resin)
Catalogue
£8.80
-
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
Gloster Meteor F.8/Fr.9 Ejection Seat (M.B. Mk.II) (designed to be used with Airfix kits)
Aircraft seats (resin)
Catalogue
£6.50
-
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
Messerschmitt Bf-109E Wheels (designed to be used with Special Hobby kits) Resin wheel full of realistic details, designed for the new and superb Bf-109E kit
Aircraft wheels (resin)
Catalogue
£4.30
-
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
Messerschmitt Bf-109E exhausts (designed to be used with Eduard kits and Special Hobby kits) These exhausts have the stubs nicely drilled out and replace the styrene ones which lack the hole detail [Bf-109E-3 Bf-109E-4]
Aircraft exhausts (resin)
Catalogue
£2.50
-
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

Henschel Hs-129B exterior set (designed to be used with Hasegawa and Revell kits)
Aircraft detailing sets (resin)
Catalogue
£18.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
Sd.Ah 115 Wheels. The new Special Armour's Sd.Ah 115 kit will benefit from using this set of finely detailed resin cast wheels which, compared with the styrene items, brings substantially higher levels of detail and much nicer tyre tread pattern. (designed to be used with Special Armour kits)
Military vehicle detailing sets (resin)
Catalogue
£8.60
-
In Stock
3