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=362960 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 31 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

No Image
Das Werk DASDW35017
Pz.Sfl.la 5cm Pak38 auf gp.Mun.Schlepper VK3.02
Military vehicle kits (injection)
Catalogue
£26.95
£24.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
Das Werk DASDW35018
Rammtiger VK45.01 (P) The Rammtiger was a proposed conversion of the VK 45.01 (P) chassis (used for the Elefant and Ferdinand tankhunter) in a heavily armoured ramming vehicle. 3 superstructures were completed but their fate is unknown as the project was cancelled in 1943
Military vehicle kits (injection)
Catalogue
£35.95
£32.36
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
Das Werk DASDW35031
90mm Kanonenjagdpanzer / Beobachtungspanzer aâé-¢ 100% new molds aâé-¢ 1:35 scale aâé-¢ highly detailed 2 in 1 kit aâé-¢ link and length tracks for the correct Diehl 828G tracks aâé-¢ accurate replicated roadwheels with details on both sides aâé-¢ PE parts included aâé-¢ Hatches in the crew compartment roof can be built open/closed aâé-¢ Transparent parts included
Military vehicle kits (injection)
Catalogue
£39.95
£35.96
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
Das Werk DASDW35012
Schwimmwagen Trippel SG6/38
Military vehicle kits (injection)
Catalogue
£33.95
-
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
Das Werk DASDW35034
LARC-V early version aâé-¢ Highly detailed static plastic model aâé-¢ Correct details for Bundeswehr and US Navy / Army early Vietnam version aâé-¢ PE parts included aâé-¢ 5 marking options
Military vehicle kits (injection)
Catalogue
£54.95
-
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
Das Werk DASDW35037
Mtl. Zgkrwg. 8t, Sd.Kfz.7 w/ Crew aâé-¢ Highly detailed static plastic model aâé-¢ Realistic representation of 1943 production Sd.Kfz.7 aâé-¢ Ultra thin plastic parts for more realism aâé-¢ Complete interior aâé-¢ Including PE- & clear parts aâé-¢ With workable single track links aâé-¢ Four paint schemes & decal options 6 lifelike soldier figures included
Military vehicle kits (injection)
Catalogue
£51.95
-
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
Das Werk DASDW35003X
Faun L900 incl. Sd.Ah. 115, 1/35 - Re-released edition both hard and soft-top including the trailer. NO TANK.
Military vehicle kits (injection)
Catalogue
£73.95
-
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
Das Werk DASDW35030
mU.Pi.Pzwg. Sd.Kfz.251/7 Ausf.B aâé-¢ Highly detailed static plastic model aâé-¢ Buildable as standard 251/7 or with 2,8cm Panzerbƒ¼chse aâé-¢ Single track links aâé-¢ Four paint schemes & decal options aâé-¢ Photo-Etch-Parts for fine extra detail included aâé-¢ Detailed representation of interior & detachable bridging elements
Military vehicle kits (injection)
Catalogue
£45.95
-
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
Das Werk DASDW35019
Schwerer Kleiner Pz.Kpfw aâé-¢ Highly detailed static plastic model aâé-¢ 2 different barrel versions included (10,5cm leFH43 & 10cm PAW) aâé-¢ Optional nightvision gear included aâé-¢ 6 marking versions offered aâé-¢ Link & Lenght tracks aâé-¢ Photo-Etch-Parts included
Military vehicle kits (injection)
Catalogue
£35.95
-
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
Das Werk DASDW35029
Sd.Kfz.250/I Ausf.B aâé-¢ Highly detailed static plastic model aâé-¢ Single track links aâé-¢ Four paint schemes & decal options aâé-¢ PE-Parts for fine extra detail included aâé-¢ Open hull interior and engine included aâé-¢ Doors can be built opened & closed
Military vehicle kits (injection)
Catalogue
£39.95
-
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
Das Werk DASDW35021
Re-released! 2 in 1 Sturmgeschƒ¼tz III Ausf.G Sturmhaubitze 42. Moulded with waffle zimmerit!
Military vehicle kits (injection)
Catalogue
£40.95
-
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
Das Werk DASDW35023
US 155 mm M1918 Howitzer aâé-¢ Highly detailed static plastic model aâé-¢ Version used by US Marine Corps aâé-¢ Can be built in transport and firing position with hitch aâé-¢ Two types of ammunition aâé-¢ Ammo, crates (transport boxes) & shell handling cradle included aâé-¢ Two types of loading rammer included aâé-¢ Breech can be built open or closed Photo-Etch-Parts included. expected mid June 2022
Military vehicle kits (injection)
Catalogue
£26.95
-
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
Das Werk DASDW35026
Raupenschlepper Ost 9 RSO/01 Type 470 aâé-¢ Highly detailed static plastic model aâé-¢ Including pre-cut single track links aâé-¢ Engine and drive train included aâé-¢ Six decal options aâé-¢ PE-Parts included
Military vehicle kits (injection)
Catalogue
£35.95
-
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
Das Werk DASDW35028
Pz.Kpfw.VI TIGER I aâé-¢ Highly detailed static plastic model aâé-¢ Actual representation of the Tiger I with steel wheels aâé-¢ Highly realistic moulded on Zimmerit aâé-¢ Rubber band tracks included for easy assembly aâé-¢ Movable suspension with torsion bars included aâé-¢ One piece lower hull included aâé-¢ Four accurately researched paint schemes and decal options aâé-¢ PE-Parts for fine detailed engine grilles included. Expected ...
Military vehicle kits (injection)
Catalogue
£49.95
-
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
Das Werk DASDW35027
Pak 40 75 mm Field Gun with 4 figures aâé-¢ Highly detailed static plastic model aâé-¢ 4 Figures in lifelike poses included aâé-¢ 7.5cm Ammo, Crates & Accessories included aâé-¢ Decals for ammo and crates aâé-¢ 3 types of wheels included aâé-¢ 3 types of optional muzzle breaks aâé-¢ 3 different decal & marking options aâé-¢ PE-Parts included aâé-¢ Optional damaged rubber bandages included
Military vehicle kits (injection)
Catalogue
£35.95
-
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
Das Werk DASDW35032
Russian T-72M aâé-¢ Highly detailed static plastic model aâé-¢ 3 versions buildable aâé-¢ Correct shaped T-72M / ƒÆ'..."V-1 / ƒÆ'..."V-2 turret aâé-¢ Buildable with & without smoke discharger aâé-¢ Workable single track links aâé-¢ Figures not included
Military vehicle kits (injection)
Catalogue
£48.50
-
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
Das Werk DASDW35025
Bruckenlegepanzer M48 A2 AVLB aâé-¢ Highly detailed static plastic model aâé-¢ All specific German Army details represented aâé-¢ Bridge can be built in any position aâé-¢ Belt type tracks included aâé-¢ 4 Bundeswehr & 1 Belgian Army decal options
Military vehicle kits (injection)
Catalogue
£74.95
-
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
Das Werk DASDW35022
French 155 mm C17S Howitzer aâé-¢ Highly detailed static plastic model aâé-¢ WWI versions, Spanish Civil War & German WWII Version aâé-¢ Can be built in transport and firing position aâé-¢ Ammo crates & shell handling cradle included
Military vehicle kits (injection)
Catalogue
£26.95
-
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

Das Werk DASDW35015
German Kugelpanzer
Military vehicle kits (injection)
Catalogue
£14.95
-
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
Das Werk DASDW35016
Gepanzerter Munitionsschlepper VK3.02. In April 1940, following a demonstration of the VK3.01, revisions were to be made to the design. This lead to the VK3.02. The VK3.02 had satisfactory movement and armour, however the crew space and the organization of the ammunition was in need of some improvement. The engine also had issues with over- revving and so the maximum speed was lowered. A great kit in its own right this would a...
Military vehicle kits (injection)
Catalogue
£21.95
-
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
Das Werk DASDW35001
1:35
Faun L900 Hardtop
Military vehicle kits (injection)
Catalogue
£37.95
-
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

Das Werk DASDW35006
1:35
Einheitsanhanger 5t German Uniform 5t Trailer WWII History of the Baumuster E 5 The Anhƒ¤nger 5T or 5 tonne trailer, known by the official nomenclature of "Einheitsanhanger 5 t Baumuster E 5" was the standard medium military trailer of the German military throughout the second world war. With a load capacity of 5 tonnes, and an empty weight of approximately 2.5 tonnes, the Anhƒ¤nger 5t could be seen hauling material any...
Military vehicle kits (injection)
Catalogue
£26.95
-
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

Das Werk DASDW35013
Pz.Kpfw.VI Ausf.B Tiger II Sd.Kfz.182 - s.Pz.Abt.505
Military vehicle kits (injection)
Catalogue
£33.95
-
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

Das Werk DASDW35014
FMG 39 / FuSE 62 D "Wƒ¼rzburg"
Military vehicle kits (injection)
Catalogue
£21.95
-
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

Das Werk DASDW35005
2cm Salvenmaschinenkanone SMK 18 - Typ 2 Highly detailed breach and carriage assemblies Working elevation and sight mechanism Choice of optical or iron sights Positionable stabilizer arms Photoetched detail parts Aside from the physical attributes evident in the existing prototype; caliber, number of barrels, overall dimensions, function of the elevation and traverse mechanisms, etc... very little is known for certain ...
Military vehicle kits (injection)
Catalogue
£17.95
-
In Stock
4

   1   |   2      »      [2]

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