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.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 1,954 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
Delivered 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
Blackdog BDOA48196
Mikoyan MiG-21MF electronics (designed to be used with Eduard kits)
Aircraft detailing sets (resin)
New Arrivals
05/03/2024
£19.99
£17.99
Out of Stock
0

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
Blackdog BDOA48197
Mikoyan MiG-21MF spine and tail and engine details (designed to be used with Eduard kits)
Aircraft detailing sets (resin)
New Arrivals
05/03/2024
£19.99
£17.99
Out of Stock
0

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
Blackdog BDOA48198
Mikoyan MiG-21MF electronic , spine, tail and engine details BIG SET contains 48196 and 48197 (designed to be used with Eduard kits)
Aircraft detailing sets (resin)
New Arrivals
05/03/2024
£31.40
£28.26
Out of Stock
0

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
Blackdog BDT35240
1:35
Sturmpanzer IV Brummbar middle/late (designed to be used with Tamiya kits)
Military vehicle detailing sets (resin)
New Arrivals
05/03/2024
£23.40
£21.06
Out of Stock
0

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
Blackdog BDT35252
Jackal 2 High Mobility Weapons Platform cargo (designed to be used with Hobby Boss kits)
Military vehicle detailing sets (resin)
New Arrivals
05/03/2024
£23.40
£21.06
Out of Stock
0

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
Blackdog BDT35256
Jagdpanzer 38(t) 'Hetzer' (designed to be used with Academy kits)
Military vehicle detailing sets (resin)
New Arrivals
05/03/2024
£21.80
£19.62
Out of Stock
0

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
Blackdog BDT35257
Raupenschlepper Ost 9 RSO/01 cargo accessories set (designed to be used with Das Werk kits)
Military vehicle detailing sets (resin)
New Arrivals
05/03/2024
£23.80
£21.42
Out of Stock
0

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
Blackdog BDT35258
British army clothes WWII
Diorama accessories (resin)
New Arrivals
05/03/2024
£18.80
£16.92
Out of Stock
0

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
Blackdog BDT35259
Schwimmwagen Trippel SG6/38 cargo (designed to be used with Das Werk kits)
Military vehicle detailing sets (resin)
New Arrivals
05/03/2024
£19.80
£17.82
Out of Stock
0

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 129-P35029
Type 3 Japanese Heavy Machine Gun 1/35 The Type 3 heavy machine gun was developed from the Hotchkiss gun. In early 20th century, the Japanese had bought the licence rights to produce the Hotchkiss machine gun, Japanese engineer Kijiro Nambu improved the original design though to meet better the Japanese Army requirements. At first was produced the Type 38 heavy machine gun, later some more design alterations led to the devel...
Military vehicle detailing sets (resin)
New Arrivals
04/03/2024
£13.70
£12.33
Out of Stock
0

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 CMF35260
German WWII Hetzer Sitting Infantry man
Figures (resin)
New Arrivals
04/03/2024
£12.99
Out of Stock
0

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 CMF35261
1:35
WWII German Jagdpanzer 38(t) 'Hetzer' Crewmember 1/35 Highly detailed resin cast figure of German tank crewmember was designed for Jagdpanzer 38(t) 'Hetzer' tank hunter. The crew member sits on the Jagdpanzer 38(t) 'Hetzer''s armoured roof with his legs rested on the sloping armour. The figure is cast in one piece with only separated head and right arm.
Figures (resin)
New Arrivals
04/03/2024
£8.99
Out of Stock
0

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 CMF35262
German WWII Hetzer Radio Operator / Gunner
Figures (resin)
New Arrivals
04/03/2024
£8.99
Out of Stock
0

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 CMF35263
German WW II Hetzer Commander 1945
Figures (resin)
New Arrivals
04/03/2024
£8.99
Out of Stock
0

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 CMK3150
Ford V 3000 S Wheels. Nicely cast, detail wheels with weighted tyres offering life-like tread are an easy replacement for the original styrene wheels that lack many of the detail offered in the kit of the German WW2 trucks (designed to be used with ICM kits)
Military vehicle detailing sets (resin)
New Arrivals
04/03/2024
£14.99
£13.49
Out of Stock
0

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 CMK4469
1:48
Lockheed PV-1 Ventura Nose and Turret Armament 1 (designed to be used with Accurate Miniatures, Academy and Italeri kits) Finely detailed machine guns for the turret and belly gunner station, a nose housing for three machine guns and the fuselage gun barrels, all of which have much better details than the kit parts.
Aircraft detailing sets (resin)
New Arrivals
04/03/2024
£8.10
£7.29
Out of Stock
0

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 CMK6008
1:35
Bell AH-1G Cobra Upgrade Cockpit Set (designed to be used with ICM kits) This multimaterial set consists of resin cast, 3D-printed and photo-etched parts and significantly improves the pilots and gunners offices of the AH-1G Cobra model. And as the interior of the cockpit is very easy to see through the large side window panes, which can even be posed open, this set is truly a aâé-~Must have! one.
Aircraft detailing sets (resin)
New Arrivals
04/03/2024
£12.40
£11.16
Out of Stock
0

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 CMK8068
SS-100 Gigant Wheels 1/48 / (designed to be used with Tamiya kits) Detail, resin cast wheels with weighted tyres and accurately portrayed tread pattern, overall much finer detail than the styrene wheels in the Tamiya kit.
Military vehicle detailing sets (resin)
New Arrivals
04/03/2024
£11.70
£10.53
Out of Stock
0

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
Planet Models PLAMV135
Fordson WOT2D aâé-~Van Body. The WOT2 light truck was produced by Ford of Britain in Dagenham since 1939 in several various versions. The production in the war years gave a total of 60,000 examples that served with the British Army, RAF as well as with the Royal Navy well until the late 1950s. During the war, these vehicles offered support to British troops in Western Europe, Africa and elsewhere. They were also operated by f...
Military vehicle kits (resin)
New Arrivals
04/03/2024
£19.20
£17.28
Out of Stock
0

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 Hobby SH48200
SNCAC NC.701 Martinet The German Siebel Si 204 was developed just before the Second World War as a modern transport aircraft. The outbreak of the war meant change in the type's purpose and it began to fly for the Luftwaffe as a trainer. This variety of the Siebel was also produced in oddupied countries, in Czechoslovakia and in France. And in both these countries, the production went on also after the war had ended. The French...
Aircraft kits (injection)
New Arrivals
04/03/2024
£54.50
£49.05
Out of Stock
0

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 Hobby SH48224
Bell AH-1Q/S Cobra aâé-~IDF Against Terrorists 1/48 The Israeli Air Force acquired and subsequently tested the AH-1G Cobra attack helicopters. Due to the need of helicopters capable of destroying armoured vehicles or the enemys fortified footholds, Israel in cooperation with its US counterpart converted these helicopters to the AH-1S version. This AH-1S airframes were equipped with the TOW missiles. Israeli helicopters s...
Aircraft kits (injection)
New Arrivals
04/03/2024
£39.60
£35.64
Out of Stock
0

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

Special Hobby SH72397
1:72
Re-released! Breda Ba.88B Lince Model of a German high altitude fighter aeroplane wearing 'what-if' colour schemes. Model of an Italian WW2 bomber aeroplane. Contents of the kit: three grey styrene sprues, one clear plastic sprue, decal sheet and full colour instruction booklet. Nicely detailed model Fancy blotch pattern camouflage schemes Decals offer markings for three Italian machines.
Aircraft kits (injection)
New Arrivals
04/03/2024
£23.60
£21.24
Out of Stock
0

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 Hobby SH72495
External armament for SMB-2 and other IAF planes (designed to be used with Special Hobby kits) Two styrene sprues that contain armament used on IAF Super Mystere SMB.2 jets as well as on other aircraft of the period two 250kg bombs, two napalm bombs each with the respective pylons, two quadruple racks with eight 100kg bomblets and a pair of Shafrir 2 AAMs with launchers and pylons.
Aircraft kits (injection)
New Arrivals
04/03/2024
£11.25
£10.13
Out of Stock
0

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 Hobby SHM72050
Heinkel He-59B MASK (designed to be used with Special Hobby kits) Pre-cut spray masks for the clear canopy parts of the Heinkel He 59B model kits produced by Special Hobby.
Aircraft paint masks (self adhesive)
New Arrivals
04/03/2024
£8.10
Out of Stock
0

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 Hobby SHM72051
1:72
Breda Ba.88B Lince MASK (designed to be used with Special Hobby kits) Pre-cut spray masks for the clear canopy parts and u/c wheels of the Breda Ba.88B Lince model kits produced by Special Hobby.
Aircraft paint masks (self adhesive)
New Arrivals
04/03/2024
£8.10
Out of Stock
0

[1]   «      52   |   53   |   54   |   55   |   56      »      [79]
-50 -20 -10 | +10 +20

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