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 ps.id = 956 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE(P.arrival_date) desc, P.code
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 18,790 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

Scale

Product Type

Manufacturer

Added Within

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

Re-printed! Royal Artillery # 2. British RA in NW Europe. The O.P. tanks. Sherman Mk.V OP, Cromwell Mk.IV OP and Humber Scout Car.
Military vehicle decals
Catalogue
£8.99
-
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
War in Ukraine #1 AZOV-Batalion 2014-2022. BRDM-2, UAZ-469, UAZ-452 'Buhanka' and MT-LB with ZSU-23 gun
Military vehicle decals
Catalogue
£6.40
-
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
War in Ukraine #2 Ukrainian AFVs 2022 war. BRDM-2, BMP-1P and BMP-2.
Military vehicle decals
Catalogue
£6.40
-
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
War in Ukraine #3 Ukrainian transport vehicles 2014-2022. MAZ-537 tank transporter and UAZ-452 'Buhanka'.
Military vehicle decals
Catalogue
£7.70
-
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
East Front Tigers s.Pz.Abt. 501 1943-44 Tiger I and Befehls-Tiger I Mid production.
Military vehicle decals
Catalogue
£6.40
-
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
Sturmgeschutz/StuG-Abt #1 Generic insignia and unit markings for the SturmgeschÆ'é»tz units. Sturmgeschutz/StuG-Abt 177, Sturmgeschutz/StuG-Abt 182, Sturmgeschutz/StuG-Abt 185, Sturmgeschutz/StuG-Abt 186, Sturmgeschutz/StuG-Abt 190, Sturmgeschutz/StuG-Abt 191, and Sturmgeschutz/StuG-Abt 197.
Military vehicle decals
Catalogue
£6.40
-
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
1:72
Sturmgeschutz/StuG-Abt #2 Generic insignia and unit markings for the SturmgeschÆ'é»tz units. Sturmgeschutz/StuG-Abt 192, Sturmgeschutz/StuG-Abt 201, Sturmgeschutz/StuG-Abt 202, Sturmgeschutz/StuG-Abt 244, and Sturmgeschutz/StuG-Abt 245.
Military vehicle decals
Catalogue
£6.40
-
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
1:72
Sturmgeschutz/StuG-Abt #3 Generic insignia and unit markings for the SturmgeschÆ'é»tz units. Sturmgeschutz/StuG-Abt 203, Sturmgeschutz/StuG-Abt 209, Sturmgeschutz/StuG-Abt 210 and Sturmgeschutz/StuG-Abt 245.
Military vehicle decals
Catalogue
£6.40
-
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
Sturmgeschutz/StuG-Abt #4 Generic insignia and unit markings for the SturmgeschÆ'é»tz units. Sturmgeschutz/StuG-Abt 243, Sturmgeschutz/StuG-Abt 249, Sturmgeschutz/StuG-Abt 259, Sturmgeschutz/StuG-Abt 270 (Skijger), Sturmgeschutz/StuG-Abt 276, Sturmgeschutz/StuG-Abt 'GD', Sturmgeschutz/StuG-Abt 905, Fallschirm PzJgAbt.1, 'FHH', Lehr schule, Lehr-Batterie 901/Sturmgeschutz/StuG-Brig 901, Lehr-schule 'Burg', Artillerie-Le...
Military vehicle decals
Catalogue
£6.40
-
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
Sturmgeschutz/StuG-Abt #5 Generic insignia and unit markings for the SturmgeschÆ'é»tz units. Sturmgeschutz/StuG-Brig 232, Sturmgeschutz/StuG-Brig 236, Sturmgeschutz/StuG-Brig 242, Sturmgeschutz/StuG-Brig 243, Sturmgeschutz/StuG-Brig 303, Sturmgeschutz/StuG-Brig 600, Sturmgeschutz/StuG-Brig 667, Sturmgeschutz/StuG-Brig 904, Sturmgeschutz/StuG-Brig 907. Sturmgeschutz/StuG-Batt. 90, Sturmgeschutz/StuG-Batt. 660, Sturmges...
Military vehicle decals
Catalogue
£6.40
-
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
Macchi Folgore set. Cockpit seat, exhausts and early intake (without sand filter) for C.202 . Replaces AHL72042. (designed to be used with Hasegawa, Hobby 2000 and Italeri kits)
Aircraft detailing sets (3D printed)
Catalogue
£8.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
Savoia-Marchetti SM.79 Sparviero engine & cowling set (standard exhausts) (designed to be used with Italeri kits) for any a/c up to 1941-42 3D-Printed set, 3 pcs (each consisting of cowling, exhaust ring and engine) 3d-printed
Aircraft detailing sets (resin)
Catalogue
£33.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
Piaggio P.1001 propeller any kit of Macchi C.200, C.202 or C.205 3D-Printed set, 2 pcs, separate spinners (one each for Folgore and Veltro) (designed to be used with Italeri and Supermodel kits)
Aircraft detailing sets (3D printed)
Catalogue
£14.50
-
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
Humber Armoured Car Mk.IV British Army Europe
Military vehicle kits (injection)
Catalogue
£13.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
Fieseler Fi-156K STORCH: ST-112 & ST-113 - IlmavE (1941 to 1955) Avro ANSON 652A Mk.I: AN-101, AN-102 & AN-103 / LeLv 48, LLv44 & IlmavE (1936 to 1944) Fouga CM.170 MAGISTER: HvLLv 13, HvLLv 21, HvLLv 31, KoulLLv & IlmaSK (1959 to 1984) [Fi-156C]
Aircraft decals (military)
Catalogue
£11.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
Soviet 2S19-M2 Self-propelled Howitzer 152mm Self-propelled Howitzer 2S19M2 "Msta-S" is an upgraded and improved version of heavy artillery 2S19 and was first shown at the 2013 Russia Arms Expo in the Russian city of Nizhny Tagil. Modifications included a new computerised fire control system for increased fire rate by automated loading, laying and retargeting, Nakidka radar-absorbent camouflage which reduces the vehicle's ther...
Military vehicle kits (injection)
Catalogue
£16.99
-
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
Lockheed U-2C Dragon Lady U-2 built in complete secrecy by Kelly Johnson and the Lockheed Skunk Works, the original U-2A first flew in August 1955. Early flights over the Soviet Union in the late 1950s provided the president and other U.S. decision makers with key intelligence on Soviet military capability. In October 1962, the U-2 photographed the buildup of Soviet offensive nuclear missiles in Cuba, touching off the Cuban Mi...
Aircraft kits (injection)
Catalogue
£33.99
-
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

Sherman M4A2 3 models. Each sprue gives options to build either a 75mm or 76mm version and comes with 2 commander figures - UK or US
Military vehicle kits (injection)
Catalogue
£26.35
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
1:32, 1:35
1001 white 1002 black 1027 gunmetal 1073 Russian green 1011 clear red 2004 grey primer
Paint, Acrylic 12ml
Catalogue
£6.60
-
In Stock
10+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
Short Sunderland Mk.I wheels and canopy frame paint mask (outside only) (designed to be used with Italeri IT1302 kits)
Aircraft paint masks (self adhesive)
Catalogue
£5.99
-
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
Martin B-26B/C Marauder wheels and canopy frame paint mask (outside only) (designed to be used with Hasegawa and Hobby 2000 kits)
Aircraft paint masks (self adhesive)
Catalogue
£5.99
-
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

Re-released! Bell Airacobra Mk.I
Aircraft kits (injection)
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

Re-released! Avro Rota C.30 Decals RAF, Yugoslavia, Norway and Czechoslovakia
Aircraft kits (injection)
Catalogue
£19.99
-
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

Re-released! Avro Rota Mk.I/C.30A Decals RAF, Sweden, Switzerland and Spain
Aircraft kits (injection)
Catalogue
£19.99
-
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
Yak-11 / C-11 The Yakovlev Yak-11 is based on the Soviet Yak-3 fighter aircraft, which flew with the AS-82FN radial engine on 10 November 1945. However, this machine did not make it into production, but was further modernized to create a new aircraft, which underwent trials in the spring of 1946. 1. C-11, akrobaticki skupina, v½robn A. 171721, Al'SLA, 1958 2. Yak-11, OK-GFB, v½robn A. 40220, Aeroklub Spi...iski Nov...
Aircraft kits (injection)
Catalogue
£22.99
-
In Stock
5+