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

No Image
Echelon FD AXT721045
1:72
Schwere Panzerabteilung 507 Tiger IIs sAbt 507 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 tracks drive...
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 D166189
Hell Hath No Fury Like An Easy Eight Scorned Markings for a specific "Easy Eight", two versions of the names provided. And two sets of bumper codes and VRN provided, one in weathered appearance, the other unweathered. Also comes with colourful unit patches.
Military vehicle decals
Catalogue
£10.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 D166219
Sandbagged Shermans of the 14th Armored Division. A very large set of decals covering well-known sandbagged M4s on the ETO, practised particularly by the 14th AD. A number of the sandbagged M4s were of the long-gun variety, mostly M4A3E8 "Easy-eights" and M4A3 (76) W. While a number were void of markings other than the stars there were several that had their bumper codes stenciled onto the barrel itself. The stencils were larg...
Military vehicle decals
Catalogue
£19.99
-
In Stock
4

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

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 AXT161040
4. Pz.Div. Pz.Kpfw.III Ausf.E, Ausf.F, Ausf.G & Ausf.J
Military vehicle decals
Catalogue
£16.30
-
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 D166294
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 eff...
Military vehicle decals
Catalogue
£14.80
-
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 D166295
2. SS & 3. SS Sturmgeschutz/StuG.III Ausf.Gs
Military vehicle decals
Catalogue
£19.99
-
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 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 FL484010
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
3

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

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