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 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 584 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
ICM ICM48311
1:48
Bristol Beaufort Mk.IA with tropical filter. Developed by the Bristol Aeroplane Company, the Bristol Beaufort torpedo bomber began to enter service with the RAF Coastal Command in late 1939. The aircraft was actively used to perform various tasks, and its peak monthly production was up to 30 machines per month. Later the Bristol Beaufort was improved, the composition of armament and equipment changed. The Mk.IA modification ha...
Aircraft kits (injection)
Catalogue
£52.60
-
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 ICM32020
1:32
Fiat CR.42 Falco, WWII Italian Fighter
Aircraft kits (injection)
Catalogue
£49.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 ICM32042
Gloster Sea Gladiator Mk.II WWII British Naval Fighter
Aircraft kits (injection)
Catalogue
£51.90
£46.71
-
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 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 ICM48100
Mistel 1 WWII German Composite Aircraft. The Mistel Strike Aircraft Complex was a high-powered guided attack aircraft. The combat component of the complex was the Ju 88 bomber, which was fitted with a large warhead with an explosive material weighing between 1,800 and 2,000 kg instead of a cockpit. The aircraft thus becomes a kind of giant bomb. The nose of the fuselage was equipped with a long tip with a detonator to create a...
Aircraft kits (injection)
Catalogue
£59.80
£53.82
-
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 ICM48304
1:48
North-American/Rockwell OV-10A Bronco US Navy. OV-10A Bronco aircraft proved themselves well during the Vietnam War. They were used as light attack aircraft for direct support of troops and as target aircraft. They were distinguished by good maneuverability, good survivability and a rather large combat load. It should also be noted the short time in which they managed to appear above the set goals. The OV-10A Bronco was part o...
Aircraft kits (injection)
Catalogue
£39.80
£35.82
-
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 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

No Image
ICM ICM72206
Mitsubishi Ki-21-Ia RTAF, Thailand's Heavy Bomber
Aircraft kits (injection)
Catalogue
£33.80
£30.42
-
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 ICM32051
Stearman PT-17 with American Cadets
Aircraft kits (injection)
Catalogue
£53.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

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

ICM ICM72173
Mikoyan MiG-25RB Soviet Reconnaissance Plane
Aircraft kits (injection)
Catalogue
£29.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 ICM72030
Tupolev Tu-2T Soviet torpedo plane The Tu-2T aircraft was a variant of the serial Tu-2 bomber, upgraded as a naval torpedo aircraft. This modification was developed and tested in the spring of 1945. It was equipped with special holders that allowed the use of two torpedoes type 45-36AN. Torpedoes could be used for naval purposes both in turn and simultaneously. During a two-torpedo volley, a special device monitored the time i...
Aircraft kits (injection)
Catalogue
£15.20
-
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 ICM32043
Gloster Gladiator Mk.I with British Pilots in Tropical Uniform
Aircraft kits (injection)
Catalogue
£55.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 CATICM24
No Scale
ICM catalogue 2024 https://icm.com.ua/wp-content/uploads/2024/01/catalogue-2024_web.pdf
Catalogues and lists
Catalogue
£1.99
Out of Stock
0

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

No Image
ICM ICM24024
Type G4 with MG 34 and German Staff Personnel Best to be used with ICM paint set ICM3038 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. The personnel of the Wehrmacht officer corps were distinguished by their high standards. At the beginning of World War II, it represented a unique, corporately closed ...
Car 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 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 ICM32045
Gloster Sea Gladiator Mk.II with Royal Navy pilots. Air forces of the Royal Navy at the beginning of World War II consisted of 20 squadrons with a total of 232 aircraft. The tasks of naval aviation included reconnaissance, spotting for battleships, offensive operations against enemy warships and merchant vessels, and protection against submarines and enemy aircraft. Squadrons typically had 9-12 aircraft of the same type, a...
Aircraft kits (injection)
Catalogue
£59.80
£53.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 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 ICM48228
Junkers Ju-88P-1 Tank Buster In early 1942, the Luftwaffe command became obvious the need to create an anti-tank aircraft that could hit the armor of Soviet medium and heavy tanks. In the summer of 1942, a decision was made to convert a small batch of Ju 88A-4 bombers into anti-tank versions, later they received the designation Ju 88P-1. In the large ventral gondola of these aircraft was a 75-mm PaK 40L anti-tank gun (also kno...
Aircraft kits (injection)
Catalogue
£59.80
£53.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 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

   1   |   2   |   3   |   4   |   5      »      [24]
| +10 +20

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