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=362939 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 230 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
Echelon FD ALT352025
A34 Comets of 23 Hussars, 11 Armoured Division Marking set covering 23 Hussars Comets of 11 Armoured Division. A couple of the tanks had names. Being the senior regiment of 11 AD, all squadron symbols would be red, thus a complete set is provided. The allied recognition star is based on a known overhead view of a 11 AD Comet, the ring being thick. Thus, different from kit samples. Generic WDN digits provided, including pre...
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD ALT352026
A34 Comets of 3 RTR, 11 Armoured Division Marking set covering 3 Royal Tank Regiment Comets of 11 Armoured Division. Probably the most photographed Comet unit during the war. Almost all had names starting with "C" regardless of which squadron. Some had personalised markings. Several AOS "52" squares and 11 AD Symbol variations were detected among the tanks, so were as faithfully captured as possible for the set. Being the sec...
Military vehicle decals
Catalogue
£16.30
-
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
Echelon FD ALT352027
A34 Comets of 2 Fife & Forfar Yeomanry, 11 Armoured Division. Marking set covering 2 Fife & Forfar Yeomanry Comets of 11 Armoured Division. Several of the tanks had names. Being the junior regiment of 11 AD, all squadron symbols would be blue, thus a complete set is provided. Large tactical numbers were seen at the turret rear of the regiment's Comets, so generics are provided. The allied recognition star is based on a...
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD AXT351041
4.Pz.Div. Tauchpanzer III Ausf.G & Ausf.H Operation Barbarossa and Prior Covers markings of various 4. Pz.Div. Tauchpanzer III Ausf. G & H versions right up to Operation Barbarossa. Markings prior to Barbarossa are 5 specific 2nd company Ausf Gs & Hs, each bear is slightly different from the other. In other words, while they looked similar from afar, each had unique details and lines. "Grisly-bar" isn't the same as the other ...
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD AXT351044
7.Pz.Div. Befehlswagen & Pz.Kpfw.III (Ausf.J, Ausf.L, Ausf.M & AusfN) Operation Citadel Marking set focused on Pz.Rgt. 25 of 7. Panzer Division Panzer IIIs that saw action at the Kursk Offensive, 1943. Otherwise known as Operation Citadel. Several of the unit's Befehlwagens (III) and Panzer IIIs were photographed with sAbt 503 Tiger Is rolling along in the background. The unit's Panzer IIIs had unique features, such as pointy...
Military vehicle decals
Catalogue
£16.30
-
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
Echelon FD AXT351045
Schwere Panzerabteilung 507 Tiger IIs sAbt 507 Pz.Kpfw.V Tiger IIs that saw action in the Paderborn area, 1945. Pecular to the unit, like their Tiger Is, the first digit of the turret numbers were larger and stood taller than the second and third digits (platoon and tank). Except that the colours were reversed - white outlines on solid black. Of interest is one of the "402"s featured on this set, which was with single-link tr...
Military vehicle decals
Catalogue
£14.80
-
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

Echelon FD D356135
Re-released! WWII .50 CAL ammo box labels (6, 7 & 8)
Military vehicle decals
Catalogue
£5.60
-
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

Echelon FD D356172
1:35
Re-printed! IDF Jerry Can Markings
Military vehicle decals
Catalogue
£5.60
-
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

Echelon FD FL354006
1:35
Reprinted! Israeli Antenna Flags & Flag Patches
Military vehicle decals
Catalogue
£7.70
-
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

Echelon FD P353019
Re-released and re-vamped! U.S. Armored Division WWII & Post War Patches (1/35)
Figure decals
Catalogue
£11.70
-
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

No Image
Echelon FD D356282
2-1 Cav M113s in Vietnam This 4th Infantry Division cavalry unit fielded standard ACAV M113s fitted with late style cupolas. 2-1 Cavalry, also known as "Black Hawks", had their tracks marked with a large black silhouette of a bird on both sides of the M113s. The same marking came be found on their M48s. Unit's early tracks used colored numbers: red for A troops; yellow for B troops; and C used blue. Later, they switched to a...
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD D356293
Marder II Sd.Kfz.131
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD D356296
M18 Hellcat 76mm GMC (Part 1) A huge marking set covering specifically 805 Tank Destroyer Battalion that operated in Italy, also seen during the liberation of Bologna, Italy 1945. Known for carrying extra large vehicle identifiers on the hull and turret. Two speciifc vehicles markings are provided plus two extra options that utililises the generic codes provided. All four had vehicle names, stenciled in Gothic styles. Evidenc...
Military vehicle decals
Catalogue
£16.30
-
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
Echelon FD D356299
GHQ Middle East Command Jeeps
Military vehicle decals
Catalogue
£12.75
-
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
Echelon FD D356300
Abteilung 905 282 Inf.Div. Sturmgeschutz/StuG.III Ausf.Gs
Military vehicle decals
Catalogue
£9.70
-
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
Echelon FD D356301
Libyan Civil War Technicals Part 2
Military vehicle decals
Catalogue
£12.75
-
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
Echelon FD FL354010
German Aerial Identification / Recognition Flags (WWII) Aerial markers (as ground identifiers at times) used on all fronts in 3 common sizes. Smaller ones are often used on turret bins or across the rear deck of smaller panzers and on the front of softskin AFVs, while the large ones were used on engine decks medium panzers. Also included is the balkenkreuz variety. To be used wholesale or partial on soft medium like tissue, th...
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD FL354011
Panzer Signal Flags and Pennants (WWII) Essential items of any panzer's inventory. FLown during a breakdown or when the panzer is in need of recovery, the breakdown flag often seen and misrepresented by wrong tones are now properly coloured: black cross over yellow background. The furhrer wimpel (command pennant) in two versions. All flags and pennants are based on documented measurements. The skull pennant, seen during the e...
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD D356287
Back in stock! German Fire Extinguisher Labels of WWII (Part 1) Finely printed, down to the exact fonts seen on actual examples. Each label is produced from an larger, more refined format down-scaled to 1/35. Whilst most text can't be seen at this level, one is assured of the highest integrity when it comes to form. Proper tan colour is used on the tan label (not substituted by yellow, an added cost but well-worth the effort f...
Military vehicle decals
Catalogue
£5.60
-
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
Echelon FD D356288
Back in stock! German Fire Extinguisher Labels of WWII (Part 2) Finely printed, down to the exact fonts seen on actual examples. Each label is produced from an larger, more refined format down-scaled to 1/35. Whilst most text can't be seen at this level, one is assured of the highest integrity when it comes to form. A number of labels on this set used gold or silver pigments. Finally, the set also includes a smaller yellow lab...
Military vehicle decals
Catalogue
£5.60
-
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
Echelon FD D356294
Sturmgeschutz/StuG.III Ausf.F, Ausf.G & StuH 42 of Sturmgeschutz/StuG. Brig. 667 A set of striking unit markings from Sturmgeschutz/StuG.Brigade 667. Emblem artwork is based on actual Sturmgeschutz/StuGs in combat. These markings were seen on their Sturmgeschutz/StuG.Ausf. F & Gs that operated on the Eastern and Western fronts. As the saying goes "the devil is in the details". While the markings are straight forward, much effo...
Military vehicle decals
Catalogue
£9.70
-
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
Echelon FD D356295
1:35
2. SS & 3. SS Sturmgeschutz/StuG.III Ausf.Gs A marking set that covers 2. & 3. SS Sturmgeschutz/StuG.III Gs. Das Reich Sturmgeschutz/StuGs operated at Kursk with one late mark that served on the western front. A number of their Sturmgeschutz/StuGs displayed card symbols in front and back of each vehicle. Each symbol probably represent battery designations. Some had names on hull sides. The Totenkopf vehicle operated some where...
Military vehicle decals
Catalogue
£9.70
-
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
Echelon FD D356297
M18 Hellcat 76mm GMC (Part 2) A huge marking set that covers a number of M18 units: 602 TD; 705 TD; 827 TD; 894 TD; & 306 ATC. Each set comes with a large suite of placards (including driver's compartment), shipping and 76mm shell end stencils. 8 variations of the .50 cal ammo box stencils. Last but not least, Tank Destroyer patches for the crew.
Military vehicle decals
Catalogue
£17.85
-
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
Echelon FD D356298
M18 Hellcat 76mm GMC (Part 3) Hellcats were used by a number of armys around the world after WW2, with some directly involved in armed conflicts. This set attempts to cover the markings that were found on most if not all of the nations' Hellcats. Each set comes with a large suite of placards (including driver's compartment) and 76mm shell end stencils. Plus 8 variations of the .50 cal ammo box stencils.
Military vehicle decals
Catalogue
£14.80
-
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
Echelon FD AXT351040
4. Panzer Division Pz.Kpfw.III Ausf.E, Ausf.F, Ausf.G & Ausf.J Covers markings of various 4. Pz.Div. Panzer III versions right up to Operation Barbarosa. White bears for 1st company, Red for 2nd and Yellow for 3rd. Also include names for 3 tanks. A number of 3rd company Ausf.Es had light blue grey base tone.
Military vehicle decals
Catalogue
£14.80
-
In Stock
5

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

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