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=7888 AND ps.id = 954 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 220 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

AFV Club AF35228
VW Schwimmwagen Type 128
Military vehicle kits (injection)
Catalogue
£39.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
AFV Club AF35023
1:35
M113A1 MRV Australian Army c.1970s"1990s The M113A1 MRV (Mechanised Reconnaissance Vehicle) was an Australian variant similar to the M113 FSV but used the turret from the FV101 Scorpion light tank rather than the older turret of the Saladin armoured car that the FSV was fitted with. The M113A1 MRV served in the Australian Army from 1976 until 1996. New tooling includes Australian Army barrel, gun mantlet, Commander and Loade...
Military vehicle kits (injection)
Catalogue
£79.99
-
In Stock
20+

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
AFV Club AF35334
US Army M34 40mm Gun Motor Carriage, Korean War Self-propelled Anti-aircraft gun. Doors can built open or closed, detailed engine and driving compartment. Injection moulded rubber tyres and track. Photo-etch parts. Adjustable-pitch 40mm AA gun can rotate 360ƒšé°. Metal gun barrel included. Gun carriage can be built in the firing or hauling position. Authentic Korean War markings.
Military vehicle kits (injection)
Catalogue
£109.99
-
In Stock
20

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
AFV Club AF35337
ROCA M110A2 203mm Howitzer Formidable long range artillery currently in service with the Taiwanese Army. 203mm howitzer with adjustable pitch angle. Comes with T65K2 & T91 rifles, plastic injection A2 barrel, cab interior structure and movable hatch. Choice of 8 markings/paint schemes. (155/203mm Howitzer Round & Stowage Case available separately, ref PKAF35017).
Military vehicle kits (injection)
Catalogue
£89.99
-
In Stock
11

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
AFV Club AF35370
1:35
US M1128 Stryker Mobile Gun System 2010 Upgrade Version US Army eight-wheeled armoured fighting vehicle of the Stryker series manufactured by General Dynamics Land Systems, based on the Canadian LAV III. Kit includes two types of machine gun shield, upgrade outdoor A/C system unit, folding ladder, I-MILES CVTESS (Instrumentable-Multiple Integrated Laser Engagement System Combat Vehicle Tactical Engagement Simulation System!)
Military vehicle kits (injection)
Catalogue
£75.99
£68.39
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
AFV Club AFAG3559
1:35
Ammo Feed Chute PE for Wiesel 1A1/A3 Mk 20 New photo-etch parts. (designed to be used with AFV Club kits)
Military vehicle detailing sets (etched)
Catalogue
£6.99
-
In Stock
8

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

AFV Club AF35294
Back in stock! FV107 Scimitar Workable Track (Late) (designed to be used with AFV Club kits)
Military vehicle tracks
Catalogue
£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
AFV Club AF35327
M54A2 5-ton US Gun Truck Truck, Cargo, 5 Ton, 6x6. US Army general purpose truck designed in the early 1950s by International Harvester and produced by IH, Diamond T, Kaiser (later Kaiser"Jeep) and Mack. The Vietnam gun trucks were field-modified weapons-platforms to protect the supplies, fuel and ammunition convoys from attack. Their armour plating was salvaged from destroyed trucks. The AFV Club kit depicts 'Eve of Destruc...
Military vehicle kits (injection)
Catalogue
£89.99
-
In Stock
10+

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
AFV Club AF35333
Diehl 828G quick-assembly workable track links for M110 series Upgrade workable track link set for M110/M107/M578.
Military vehicle tracks
Catalogue
£24.99
-
In Stock
8

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
AFV Club AF35342
Churchill Mk IV AVRE with SBG Bridge WWII Small Box Girder (SBG) bridge emplaced during the Normandy landings in June 1944 by the 1st Assault Brigade Royal Engineers using modified Churchill AVRE tanks. Weighing 4 tons and rated up to 40 tons loading capacity, the 34ft-long hinged assault bridge proved itself capable of conveying heavy Allied forces vehicles across a 30ft gap (e.g. bomb crater, anti-tank ditch), or up and ove...
Military vehicle kits (injection)
Catalogue
£109.99
-
In Stock
10+

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
AFV Club AF35110
U.S. Army M110 Howitzer 8-inch (203mm/25.3 calibre) self-propelled howitzer with recoil anchor. The first M110 battalion was established in 1963. Deployed to Vietnam in 1965. The M110 served in the US Army until 1977 when upgraded to M110A1 (203mm/37 calibre). Workable hydraulic rear recoil spade, movable loader/rammer feed tray & elevation arm. Metal gun barrel with rifling, detailed breechblock. Driver's station with inter...
Military vehicle kits (injection)
Catalogue
£89.99
-
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
AFV Club AF35184
1:35
105mm Howitzer Ammunition crates and containers to be used with M2A1 M101 and M101A1
Diorama accessories (injection)
Catalogue
£17.99
-
In Stock
6

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
AFV Club AF35306
US T130E1 Workable Track Link for M113 APC Upgrade track set for 1/35 scale AFV Club M113 Armoured Personnel Carriers & M730A1 SAM launcher.
Military vehicle tracks
Catalogue
£16.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
AFV Club AF35307
1:35
German Diehl Defence Workable Track Links for NATO M109 Upgrade track set for 1/35 scale AFV Club M109 Self-propelled Howitzer (NATO) based on track design by German arms manufacturer Diehl. New! Plastic parts injected in ABS & PS to prevent movable parts sticking. Make approx 80 links.
Military vehicle tracks
Catalogue
£16.99
-
In Stock
10+

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
AFV Club AF35332
US T132E1 Workable Track Link for M110/M107 Upgrade track set for new 1/35 scale AFV Club kit AF35110.
Military vehicle tracks
Catalogue
£16.99
-
In Stock
10+

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
AFV Club AF35343
US M1126 Stryker CROWS-J with FGM-148 Javelin Common Remote Weapon Station (CROWS-J) consists of the 8-wheel drive 'Stryker' Infantry Carrier Vehicle (ICV) equipped with Javelin anti-tank missile system. New tooling FGM-148 Javelin, turned metal missile body with photo-etch fin & new tooling ground mount. Stryker ICV includes cable cutter. Doors & hatches can be positioned open or closed.
Military vehicle kits (injection)
Catalogue
£62.99
-
In Stock
10+

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
AFV Club AF35345
Centurion Hush Puppy Quick Assembly Link & Length Track Upgrade track set suitable for 1/35 scale AFV Club Centurion armoured vehicles. New tooling.
Military vehicle tracks
Catalogue
£22.99
-
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
AFV Club AF35355
US/UK AAWS-M FGM-148 "Javelin" Portable Anti-tank Missile System Portable fire-and-forget medium-range anti-tank missile system. Infrared guidance system and high-explosive anti-tank (HEAT) warhead are capable of destroying tanks by attacking from above. Range 2.5 km. Introduced in 1996 by the United States and first used in 2003 in the Iraq War. New tooling injection moulded US M98A2 & UK Command Launch Units (CLU). Includ...
Diorama accessories (injection)
Catalogue
£25.99
-
In Stock
10+

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
AFV Club AF35395
1:35
Back in stock! British Army Centurion Mk V AVRE The Centurion Main Battle Tank first introduced in the early post-war period was developed through thirteen basic variants and became one of the all-time longest-serving tank designs. Work on replacing the wartime Churchill AVRE was completed in 1957 with the Centurion Mk 5 AVRE FV4003 which featured the short barrel 165mm demolition cannon and dozer blade. After trials in 1962 ...
Military vehicle kits (injection)
Catalogue
£69.99
-
In Stock
10+

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
AFV Club AFAG3525
German 15cm Heavy Infantry Gun Ammo Set (brass) Ammo set for 1/35 scale 15cm sIG 33 gun, also fitted to Pz.Kpfw.1B, Pz.Kpfw.II (Sf) etc. 8 brass shells (2 types 4 each), plus photo-etch fired & unfired shell case headstamps.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
20+

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
AFV Club AFAG3534
British 95mm Howitzer Ammo Set (brass) Ammo set for 1/35 scale Churchill Mk V & Cromwell Mk IV. 16 brass shells (4 types 4 each), 4 x brass cartridges, photo-etch headstamps & decals.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
20

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
AFV Club AFAG3543
German SFH18 Howitzer Ammunition (brass) Ammo set for 1/35 scale WWII German 15cm howitzer. 21 brass shells of type 19, 36 & 38 (3 each).
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
19

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
AFV Club AFAG3547
NATO M68/L7 105mm Tank Ammunition (brass) Ammo set for 1/35 scale US M68 105mm gun derived from Royal Ordnance L7, Cold War & later. Fitted to British Centurion from Mk 5/2, German Leopard 1, certain variants of US M48/M60. 12 brass shells of type M392, M416, M456A2, M494, M728, M774 plus photo-etch headstamps.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
15

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
AFV Club AFAG3551
US 120mm Ammo Set (aluminum) Ammo set for 1/35 scale M256 gun of US M1A1/M1A2 Abrams. 10 aluminium shells of Type M829, M829A3, M830A1, M865, M1002 (2 each) & 2 x M1028 canisters, plus photo-etch headstamps.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
13

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
AFV Club AFAG3553
US LST 491 Class Bridge Detail Upgrade Set (photo-etch) Conversion kit for US Navy LST-491 Landing Ship, Tank. Photo-etch bridge detail parts for LST-495, 504, 505, 506, 511, 541.
Ship detailing sets (etched)
Catalogue
£9.99
-
In Stock
2

   1   |   2   |   3   |   4   |   5      »      [9]

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