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=8060 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 270 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
ICM ICM35136
Unimog S 404 with box body, German military truck. The Unimog S military vehicle was used by units of the Bundeswehr to solve a wide range of tasks. In addition to the standard cargo version, the version of the car with a van body also became widespread. Gias, Harmenig, Zweirad-Union, BINZ, Voll and others were engaged in the production of these closed bodies. About 16,000 cars were equipped with such body options. Many of the...
Military vehicle kits (injection)
Catalogue
£37.80
£34.02
-
In Stock
20+

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
ICM ICM35409
V3000S 'Einheitsfahrerhaus', WWII German *new parts. The V3000S belonged to a widespread part of the Wehrmacht truck fleet with a payload of 3 tons. The 95-hp V8 gasoline-powered truck was assembled at Ford's Cologne plant in 1941 and production continued until May 1945. It was used by all branches of Germany military forces - the Army, Air Force and Navy. Cooling of the V8 engines was quite problematic, so later versions were...
Military vehicle kits (injection)
Catalogue
£37.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
ICM ICM35599
WWII US Military Patrol (G7107 with MG M1919A4
Military vehicle kits (injection)
Catalogue
£49.80
£44.82
-
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
ICM ICM53055
Sikorsky CH-54A "Tarhe" with BLU-82/B "Daisy Cutter" bomb. The CH-54A heavy helicopter was successfully used by the US Army in Vietnam during the war. It carried a variety of weapons, ammunition, vehicles including excavators, bulldozers, trucks and boats. In addition to carrying combat equipment, the CH-54A was also used as a carrier for the M-121 super-heavy bomb. Such bombs were dropped in order to quickly clear the forest...
Aircraft kits (injection)
Catalogue
£159.80
£127.84
-
In Stock
20+

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

ICM ICM35517
Soviet ZiL-131 KShM, Soviet Army Vehicle
Military vehicle kits (injection)
Catalogue
£35.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
ICM ICM3050
1:32, 1:35, 1:48
WWII British Royal Navy Aviation Acrylic paint set for (designed to be used with ICM kits) [Gloster Sea Gladiator Mk.II]
Paint, Acrylic 12ml
Catalogue
£7.40
£6.66
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
ICM ICM3051
1:32, 1:35, 1:48
WWI US Vehicles Acrylic paint set (designed to be used with ICM kits) [Standard B 'Liberty' truck FWD Type B Model T 1917 Ambulance]
Paint, Acrylic 12ml
Catalogue
£7.40
£6.66
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
ICM ICM35656
FWD Type B WWI US Ammunition Truck. The American company FWD (Four Wheel Drive Auto Company) was once an innovative enterprise that developed and produced four-wheel-drive vehicles. One of these cars, the 3-ton FWD Type B truck, had a classic hoodless layout with the driver's position above the engine compartment. The driver was located directly on the hood of the 4-cylinder engine with a capacity of 36 hp, the radiato...
Military vehicle kits (injection)
Catalogue
£34.99
£31.49
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
ICM ICM35755
'War has no gender'. Female servicemen of the Armed Forces of Ukraine (100% new molds) As of October 2023, there were almost 43 thousand female servicemen in the Armed Forces of Ukraine. Ukraine has one of the highest rates of women in the armed forces among the armies of the world, including NATO armies As of October 2023, there were almost 43,000 female servicewomen in the Armed Forces of Ukraine. Ukraine has one of the high...
Figures (injection)
Catalogue
£13.80
£12.42
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
ICM ICM3049
1:32, 1:35, 1:48
Wehrmacht Afrika Korps Acrylic paint set x 6 12ml bottles [Sd.Kfz.251/8 Ausf.A s.E.Pkw Kfz.70]
Paint, Acrylic 12ml
Catalogue
£7.40
£6.66
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
ICM ICM35015
Kozak-2 Ukrainian National Guard BRAVE UKRAINE *new parts The Kozak-2 armoured vehicle was developed by the Ukrainian Research and Production company 'Practika'. It belongs to the MRAP class of combat vehicles designed to protect the crew from mine explosions and ambushes, and its armour protection meets the second level of the STANAG 4569 standard. The armoured vehicle is built on the chassis of a four-wheel drive truck ad...
Military vehicle kits (injection)
Catalogue
£39.80
£35.82
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
ICM ICM35490
1:35
Studebaker US6-U3 US military truck *new parts The Studebaker US6 three-axle truck was launched by the Studebaker Corporation in 1941, and in 1943, REO Motor Car also joined the production. In total, about 200,000 vehicles of thirteen modifications were produced. The car had a robust design and a large payload. Its 94 hp Hercules JXD 6-cylinder engine provided the Studebaker US6 with high off-road performance. Most of these tr...
Military vehicle kits (injection)
Catalogue
£39.80
£35.82
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
ICM ICM3040
1:32, 1:35, 1:48
Armed Forces of Ukraine Acrylic paint set x 6 12ml bottles [Kozak-2 Ukrainian]
Paint, Acrylic 12ml
Catalogue
£7.40
-
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
ICM ICM3045
1:32, 1:35, 1:48
WWI French infantry Acrylic paint set x 6 12ml bottles
Paint, Acrylic 12ml
Catalogue
£7.40
£6.66
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
ICM ICM3046
1:32, 1:35, 1:48
Luftwaffe pilots Acrylic paint set x 6 12ml bottles
Paint, Acrylic 12ml
Catalogue
£7.40
£6.66
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
ICM ICM35113
Sd.Kfz.251/8 Ausf.A 'Krankenpanzerwagen', WWII German Ambulance. The Sd.Kfz.251 semi-tracked armoured personnel carrier was the main armoured vehicle of the Wehrmacht. It was designed by Hanomag in 1938 and mass production started the following summer. The armoured personnel carriers were in service with the German army throughout the Second World War and were used in all theatres of operations, with a total quantity of 15,252...
Military vehicle kits (injection)
Catalogue
£37.80
£34.02
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
ICM ICM35415
AHN 'Gulaschkanone', WWII German mobile field kitchen *new parts. To provide hot meals to Wehrmacht soldiers in the field, company kitchens were set up to prepare meals. Qualified cooks were in charge of these issues, who also used the services of assistants. The cooked food was distributed either directly into soldiers' pots or into special large thermoses (for delivery to units located at a certain distance from the kitchen)...
Military vehicle kits (injection)
Catalogue
£37.80
£34.02
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
ICM ICM3039
1:32, 1:35, 1:48
Acrylic paint set Military Equipment of the Armed Forces of Ukraine Acrylic paint set x 6 12ml bottles (designed to be used with ICM kits)
Paint, Acrylic 12ml
Catalogue
£7.40
-
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
ICM ICM3043
1:32, 1:35, 1:48
Acrylic paint set for WWI & WWII Weapon and equipment Acrylic paint set x 6 12ml bottles
Paint, Acrylic 12ml
Catalogue
£7.40
-
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
ICM ICM35410
V3000S/SSM Maultier Einheitsfahrerhaus The V3000S with a 95 hp V8 gasoline engine was launched at the Ford plant in Cologne in 1941. Production continued until the end of World War II, with a total production run of more than 25,000 vehicles. This truck was used by the German army, air force and navy. The vehicle had only rear-wheel drive, so it had insufficient off-road capability. To solve this problem, about 14,000 of thes...
Military vehicle kits (injection)
Catalogue
£39.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
ICM ICM35754
1:35
'Always the First.' Air assault troops of the Armed Forces of Ukraine The Air Assault Forces of Ukraine were created in 2017 on the basis of the existing Highly Mobile Landing Troops, which at that time already had its own separate command. According to their purpose, the units and subunits of the Air Assault Forces are divided into airborne and air assault troops; to enhance their strike potential, tank units were added to t...
Figures (injection)
Catalogue
£13.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
ICM ICM53102
Helicopter Ground Personnel (Vietnam War) The Vietnam War is often referred to as the "Helicopter War," a name that emerged as a result of their active participation in the armed conflict in Indochina. U.S. Army helicopters performed a wide range of different missions, including troop transportation, reconnaissance, fire support, cargo transportation, and search and rescue of downed aircraft crews. A large number of ground p...
Figures (injection)
Catalogue
£11.80
-
In Stock
20+

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
ICM ICM3041
1:32, 1:35, 1:48
Armed Forces of Ukraine (camouflage uniform) Acrylic paint set x 6 12ml bottles
Paint, Acrylic 12ml
Catalogue
£7.40
-
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
ICM ICM3044
1:32, 1:35
SEPTEMBER RELEASE! WWI German infantry Acrylic paint set x 6 12ml bottles
Paint, Acrylic 12ml
Catalogue
£7.40
-
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
ICM ICM35542
Typ 320 (W142) Cabriolet Soft Top, WWII German staff car The Mercedes-Benz 320, also known as the W142, was developed at the design bureau of Daimler-Benz AG and began production in 1937. It had a six-cylinder engine with a volume of 3.2 liters (3.4 liters in later versions) and 78 horsepower. The model was available with a short (2880 mm) or long (3300 mm) wheelbase and various body styles. The Cabriolet body variant was avai...
Military vehicle kits (injection)
Catalogue
£32.99
-
In Stock
5+

   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