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 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 40 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

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 DASDW16005
1:16
Sd.Kfz.251/1 Ausf.D An amazingly detailed 1/16 scale kit of this famous WWII German half-track aâé-¢ 100% DAS WERK Production (100% new moulds) aâé-¢ Workable single link tracks aâé-¢ Detailed engine with open engine cover plates aâé-¢ Detailed brake system and fuel system aâé-¢ Side boxes and storage compartments with detailed doors can be shown open or closed aâé-¢ Detailed cooling and exhaust system aâé-¢ Rear crew door can be sh...
Military vehicle kits (injection)
Catalogue
£119.00
-
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 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 DASDW16003
Sturmgeschutz/StuG.III Ausf.G early version with Winter Tracks aâé-¢ Highly detailed static plastic model aâé-¢ Workable Winterketten tracks & suspension aâé-¢ Hatches can be built open & closed aâé-¢ Basic interior aâé-¢ PE parts included aâé-¢ Cable aâé-¢ 7 marking options Optional metal barrel 7,5cm StuK L/48* *First production run only
Military vehicle kits (injection)
Catalogue
£119.00
-
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 DASDW16004
Sturmgeschutz/StuG.III Ausf.G Winter Tracks from DASDW16003. By popular request, Das Werk are releasing the Winter Tracks for the 1/16 Sturmgeschutz/StuG.as a separate item. These will also fit the Das Werk kits DW16001 Sturmgeschutz/StuG.and DW16002 Pz.Kpfw. III (designed to be used with Das Werk kits)
Military vehicle tracks
Catalogue
£19.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 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 DASDWF011
U-Boat SM-U9 WWI German Bridge Crew Set (designed to be used with Das Werk kits)
Figures (resin)
Catalogue
£10.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
Das Werk DASDWF012
U-Boat SM-U9 WWI German Deck Crew Set (designed to be used with Das Werk kits)
Figures (resin)
Catalogue
£10.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
Das Werk DASDW16001
Sturmgeschutz/StuG.III Ausf.G early version
Military vehicle kits (injection)
Catalogue
£119.00
-
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 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 DASDW16002
1:16
Pz.Kpfw.III Ausf.J 3 in 1 aâé-¢ with or without add on armour aâé-¢ with a long or short barrel aâé-¢ the Afrika Korps (DAK)-Version or Standard-Version
Military vehicle kits (injection)
Catalogue
£129.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 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

   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