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=362459 AND ps.id = 956 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 51 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

The Plastic Soldier Company WW2020003
Late War German Infantry 1943-45 (NOW BAGGED WITH HEADER CARD NOT BOXED) 54 hard plastic 1/72 scale miniatures depicting WW2 Late War German Infantry 1943-45 as follows: 6 junior officers/NCOs 36 grenadiers 6 light machine gun teams
Figures (injection)
Catalogue
£18.35
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
The Plastic Soldier Company WW2V20037
British Humber Armoured Car. This hard plastic kit contains 3 vehicles with 6 commander figures and has options to build either Mk.II or Mk.IV variants
Military vehicle kits (injection)
Catalogue
£23.72
-
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

The Plastic Soldier Company WW2V20028
Re-released! British Valentine Tank . Three models in the box. Contains options to build Mk.II, Mk.III or Mk.IX versions and has British and Soviet commander figures
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20013
3 x M5 Halftracks with options to build either M5 or M5A1 versions with 8 British/Commonwealth crew figures per vehicle and extra stowage
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2020006
1:72
American Infantry 1944-45 (NOW BAGGED WITH HEADER CARD NOT BOXED) U.S. Infantry for the European Theatre of Operations 1944-45. 51 hard plastic figures including variety of riflemen, BAR gunners, command, signallers, medics, casualties and snipers with Springfield rifles.
Figures (injection)
Catalogue
£18.35
-
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

The Plastic Soldier Company WW2V20004
1:72
Sherman M4A1 75mm Tank Easy Assembly. (NOW BAGGED WITH HEADER CARD NOT BOXED) 3 x 1/72nd Easy Assembly Sherman M4A1 75mm Tanks with British Commander figures and sand skirt options. Suitable for British and Commonwealth forces from El Alamein in 1942 onwards. Please note that this set does not come with a U.S. tank commander or 76mm tank gun.
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20019
1:72
Pz.Kpfw. 38(t) and Marder options. Each sprue has options to also build a Marder Sd.Kfz.139 or Marder Sd.Kfz.138 Ausf.H self propelled tank destroyer and there are 3 separate crew sprues each comprising 10 various crew figures. 3 models in a box and 30 crew figures.
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20023
A9/A10 British Cruiser Tank. This box set includes 3x models and include desert options and western Europe/desert tank commander options. (NOW BAGGED WITH HEADER CARD NOT BOXED)
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20026
Allied Stuart I Honey and M3 Tank 3 Allied Stuart M3 Tanks, with options to build early and late production M3 and M3A1 versions plus British and U.S. tank commander figures
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20031
Steyr Heavy Car. 3 models, 18 crew figures. Each sprue contains options to build early or late variants, open windows, canvas roof up or down and contains crew figures.
Military vehicle kits (injection)
Catalogue
£26.35
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

The Plastic Soldier Company WW2020002
Late War British Infantry 1944-45 (NOW BAGGED WITH HEADER CARD NOT BOXED) 66 hard plastic 1/72 scale miniatures depicting WW2 Late War British Infantry 1944-45 as follows: 9 junior officers/NCOs 51 riflemen/SMGs (3 can be converted to additional bren gunners) 6 light machine guns with loaders
Figures (injection)
Catalogue
£18.35
-
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

The Plastic Soldier Company WW2V20034
Sherman M4A2 3 models. Each sprue gives options to build either a 75mm or 76mm version and comes with 2 commander figures - UK or US
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2020007
U.S. Infantry Heavy Weapons. 56 figures and 18 models (3 each M1917 heavy machine guns, .3 and .5 machine guns, bazooka teams, 50mm, 81mm and 4 inch mortars
Figures (injection)
Catalogue
£20.35
-
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

The Plastic Soldier Company WW2V20002
Pz.Kpfw.IV Easy Assembly plastic injection moulded 1/72nd German Panzer IV tank. Three vehicles in the box and each sprue gives options to build either a Pz.Kpfw.IV Ausf.F1, Ausf.F2, Ausf.G or Ausf.H version and comes with 2 commander figures
Military vehicle kits (injection)
Catalogue
£26.35
-
In Stock
7

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

The Plastic Soldier Company WW2G20007
British 25pdr and CMP Quad Tractor
Military vehicle kits (injection)
Catalogue
£26.35
-
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
The Plastic Soldier Company WW2V20036
Russian Gaz trucks x 6 (three GAZ-AA Trucks and three GAZ-AAA Trucks)
Military vehicle kits (injection)
Catalogue
£35.35
-
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
The Plastic Soldier Company WW2V20035
Sd.Kfz.250 'Neu' Halftrack. Three Sd.Kfz.250 "Neu" halftracks. Each sprues includes options to build variants Sd.Kfz.250/7 mortar carrier, Sd.Kfz.250/8 Stummel, Sd.Kfz.250/9 recce, Sd.Kfz.250/10 Pak 36 with stielgranate and eight crew figures. (NOW BAGGED WITH HEADER CARD NOT BOXED)
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2020001
Soviet Infantry in Summer Uniform (WWII) (NOW BAGGED WITH HEADER CARD NOT BOXED) 57 hard plastic 1/72 scale miniatures depicting WW2 Russian infantry in summer uniform as follows: 6 junior officers/NCOs 45 riflemen/SMGs 6 light machine guns with loaders
Figures (injection)
Catalogue
£18.35
-
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

The Plastic Soldier Company WW2020004
Soviet Heavy Weapons (WWII) (NOW BAGGED WITH HEADER CARD NOT BOXED) 39 hard plastic 1/72 scale miniatures and 12 models depicting WW2 Soviet infantry heavy weapons as follows: 3 maxim teams firing 3 maxim teams moving 3 x 50mm mortar teams 3 x 82mm mortar teams 3 firing PTRS anti tank rifles 3 moving PTRS anti tank rifles
Figures (injection)
Catalogue
£18.35
-
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

The Plastic Soldier Company WW2020005
Late War German Heavy Weapons 45 hard plastic 1/72nd miniatures and 9 models depicting WW2 Late War German infantry heavy weapons as follows: 3 tripod MG42 teams firing 3 x 82mm mortar teams 3 x 120mm mortar teams 3 firing panzershrecks 3 moving panzershrecks 3 moving panzerfausts 3 firing panzerfausts
Figures (injection)
Catalogue
£20.35
-
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

The Plastic Soldier Company WW2G20006
British 25pdr with Morris Quad Tractor
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20029
20mm Sexton self propelled gun 3 Sexton Mk.2 models on the box. Each sprue contains options to be build early or late variants and crew figures (NOW BAGGED WITH HEADER CARD NOT BOXED)
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20032
Pz.Kpfw.VI Tiger I Easy Assembly plastic injection moulded 1/72nd German Tiger I tank. Three vehicles in the box and each sprue gives options to build either a early (including tropical air filter), mid or late production version and comes with 3 crew figures and a variety of stowage
Military vehicle kits (injection)
Catalogue
£26.35
-
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

The Plastic Soldier Company WW2V20021
German stowage and tank commanders (NOW BAGGED WITH HEADER CARD NOT BOXED) (3 sprues of assorted stowage and figures)(WWII) Lots of German early, mid and late war tank commanders and general vehicle drivers and passengers plus a huge variety of stowage to customise your vehicles.
Military vehicle kits (injection)
Catalogue
£20.35
-
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

The Plastic Soldier Company WW2V20033
British Universal Carrier Variants 7 models, 35 crew figures. Parts to build 3 inch mortar, Armoured Observation Post, Vickers MG, 2 inch mortar, .50 cal MG and 2 x Wasp flamethrower variants
Military vehicle kits (injection)
Catalogue
£39.35
-
In Stock
10+

   1   |   2   |   3      »      [3]

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