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 ps.id = 955
		 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 20 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

CMK/Czech Master Kits CMF48266
Soviet WWII Scout. WAS é£9.20. NOW BEING CLEARED!! SAVE 1/3RD!!!Detailed cast figure of a member of Soviet landing force that were carried on Soviet tanks in the WWII period. The figure consists of five parts, the hands are to be attached to the main body cast. The scout could be put on top of the tank behind the turret, his hands holding the handles on he turret.He is wearing a masking overall over his uniform and has a PPSh-...
Figures (resin)
Special Offers
£6.13
-
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

CMK/Czech Master Kits CMK4015
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

CMK/Czech Master Kits CMK4027
1:48
Soviet bombs WWII FAB-100 and FAB-250 WAS é£13.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£9.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

CMK/Czech Master Kits CMK4039
1:48
Focke-Wulf Fw-190A-8/R8 conversion (designed to be used with Tamiya kits) WAS é£23.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£15.73
-
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

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

CMK/Czech Master Kits CMK4105
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

CMK/Czech Master Kits CMK4123
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

CMK/Czech Master Kits CMK4196
1:48
Saab JAS-39D Gripen undercarriage (designed to be used with Italeri kits) for use with modified JAS-39B Nato versions WAS é£17.30. TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£11.53
-
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

CMK/Czech Master Kits CMK4212
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

CMK/Czech Master Kits CMK4217
Daimler-Benz DB-603 German aircraft engine WWII WAS é£14.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft engines and propellers (resin)
Special Offers
£9.47
-
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

CMK/Czech Master Kits CMK4286
Mitsubishi A6M3 Zero Armament set set (designed to be used with Tamiya kits) WAS é£10.80. NOW BEING CLEARED!! SAVE 1/3RD!!! (discontinued February 2024)
Aircraft guns (resin)
Special Offers
£7.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

CMK/Czech Master Kits CMK4292
Mitsubishi A6M5a Zero Armament set (designed to be used with Tamiya kits) WAS é£11.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft guns (resin)
Special Offers
£7.53
-
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

CMK/Czech Master Kits CMK4295
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

CMK/Czech Master Kits CMK4300
1:48
WWII Luftwaffe 900L Drop Tank (2 pc) WAS é£10.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£7.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

CMK/Czech Master Kits CMK4314
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

CMK/Czech Master Kits CMK4341
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

CMK/Czech Master Kits CMK4342
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

CMK/Czech Master Kits CMK8019
1:48
Pz.Kpfw.III Ausf.L 'Control tank' (designed to be used with Tamiya kits) WAS é£8.33. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle conversions (resin)
Special Offers
£5.55
-
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

CMK/Czech Master Kits CMK8056
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
CMK/Czech Master Kits CMK8060
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+


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