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.product_category_id = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 31 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

ICM ICM35002
Soviet Six-Wheel Army Truck with Shelter USUALLY é£35.80 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£23.87
-
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

ICM ICM35003
AR-2 (43105) Ukraine Hose fire truck USUALLY é£49.80 TEMPORARILY SAVE 1/3RD!!!
Fire engine kits (Injection)
Special Offers
£33.20
-
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 ICM35137
Unimog S 404, German Military Radio Truck. USUALLY é£37.80. TEMPORARILY SAVE 1/3RD!!! In addition to the standard truck versions of the Unimog S, there was quite common among army cars a body- van ('Koffer') variant. Some 16,000 vehicles were fitted with these body variants, and they had the longest service life among Unimog S series truck. Most of these vehicles were used as radio communication machines. They were easily dist...
Military vehicle kits (injection)
Special Offers
£25.20
-
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

ICM ICM35330
1:35
Leichttraktor Rheinmetall 1930, German Tank (100% new molds) "This is featured in World Of Tanks" WAS é£24.99. (2024 price is é£31.80) TEMPORARILY SAVE 1/3RD!!! After the end of World War I, Germany was restricted in military development by the Treaty of Versailles. However, a secret program under the name cover "Traktor" was developing armoured military vehicles and artillery. Its engine was mounted inside the front portio...
Military vehicle kits (injection)
Special Offers
£16.66
-
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 ICM35337
FCM 36, French Light Tank in German Service USUALLY é£34.99 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£23.33
-
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 ICM35339
1:35
Marder I on FCM 36 base, WWII German Anti-Tank Self-Propelled Gun USUALLY é£32.80. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£21.87
-
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 ICM35343
"Prost!", Between Battles on Bergepanther (WWII German Tankmen with Bergepanther) USUALLY é£39.80 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£26.53
-
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 ICM35354
Soviet OT-34/76 WWII Soviet flamethrower tank WAS é£32.80. TEMPORARILY SAVE 1/3RD!!! The OT-34/76 tank was a serial modification of the T-34/76 tank with a flamethrower installed in the frontal sheet of the hull. Aiming the flamethrower at the target was carried out by turning the entire tank. In the armored case, a 105-liter container was installed for a special fire mixture, it was located at the site of the radio station, w...
Military vehicle kits (injection)
Special Offers
£21.87
-
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 ICM35419
AHN2 French truck WAS é£35.80. TEMPORARILY SAVE 1/3RD!!! In 1939 Renault began developing hoodless AH series trucks for the French army. One of them was the AHN with a capacity of 3.5 tons. The first prototype was released in February 1940. The car had a fairly simple design, with a flat cab without a hood. Its serial production began in March 1941, already under the leadership of the German occupation administration. After th...
Military vehicle kits (injection)
Special Offers
£23.87
-
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 ICM35535
Packard Twelve (Model 1936), WWII Soviet Leader's Car with Passengers (4 figures) USUALLY é£31.20. TEMPORARILY SAVE 1/3RD!!!
Car kits (injection)
Special Offers
£20.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 ICM35570
Laffly V15T WWII French Artillery Towing Vehicle WAS é£32.80. TEMPORARILY SAVE 1/3RD!!! (100% new molds) The Laffly V15T four-wheel drive vehicle was created by Laffly as a light artillery tractor for French motorized infantry units. Its main purpose was to tow the Hotchkis 25mm anti-tank gun. Like other Laffly developments, the car was equipped with an unusual chassis - it had small auxiliary wheels in the nose and under the...
Military vehicle kits (injection)
Special Offers
£21.87
-
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 ICM35572
Laffly V15T with Hotchkiss machine gun USUALLY é£32.80 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£21.87
-
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 ICM35584
le.gl. Einheitz-Pkw Kfz.4, WWII German Light Anti-Aircraft Vehicle WAS é£32.80. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£21.87
-
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 ICM35591
BM-13-16 on W.O.T. 8 chassis, WWII Soviet MLRS USUALLY é£43.80 TEMPORARILY SAVE 1/3RD!!!
Figures (injection)
Special Offers
£29.20
-
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 ICM35593
G7107 4x4 WWII Army Truck (100% new molds 2021) WAS é£35.80. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£23.87
-
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 ICM35594
G7117 with WWII Soviet Drivers USUALLY é£39.80 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£26.53
-
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 ICM35596
1:35
BM-13-16 on G7107 chassis with Soviet crew WAS é£45.80. TEMPORARILY SAVE 1/3RD!!! B The most common all-wheel drive 6-wheel truck in the Soviet Army during the 1980s had a diesel engine and a cab-over-engine layout. Its serial production began in 1981 at a plant in Naberezhnye Chelny. The car was distinguished by its high reliability and off-road capability. The basic model was intended, first of all, for the transportation of...
Military vehicle kits (injection)
Special Offers
£29.98
-
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 ICM35597
G7117 US military truck WAS é£35.80. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£23.87
-
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 ICM35622
American Fire Truck Crew (1910s)WAS é£9.80. TEMPORARILY SAVE 1/3RD!!! (100% new molds) In the early 1870s, the United States experienced two of the largest fires in the country's history, the Chicago Fire and the Boston Fire, which killed hundreds and destroyed many buildings. These events have prompted an increased focus on training and education of firefighters. In particular, by the end of the 19th century, training centers...
Figures (injection)
Special Offers
£6.53
-
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 ICM35720
Brewster Body Shield, US Infantry WWI (100% new molds) WAS é£15.80. TEMPORARILY SAVE 1/3RD!!!
Figures (injection)
Special Offers
£10.53
-
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 ICM35721
WWI Italian Infantry in armor WAS é£43.80. TEMPORARILY SAVE 1/3RD!!!(100% new molds) During the WWI so-called "death companies" were created in the Italian army to carry out assault operations. They were manned by volunteers, and according to their purpose were to pave the way for the attacking infantry units. Their primary task was to clear the passages in the enemy's barriers, for which the fighters of these companies were e...
Figures (injection)
Special Offers
£9.20
-
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 ICM35795
RS-132 Ammunition Boxes (100% new molds) (designed to be used with ICM kits) [BM-13-16 on W.O.T. 8 chassis] WAS é£11.80. TEMPORARILY SAVE 1/3RD!!!
Diorama accessories (injection)
Special Offers
£7.87
-
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 ICM35906
Chernobyl #6. Feat of Divers (3 figures) (100% new molds) WAS é£17.40. TEMPORARILY SAVE 1/3RD!! In the first days after the accident at the Chernobyl nuclear power plant, firefighters fought the blaze by pouring water over the fire and sprinkling the ignition chambers with sand and pebbles. This helped to prevent potentially terrible consequences. Five days later, however, the situation became more complicated as the reactor c...
Figures (injection)
Special Offers
£11.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
ICM ICM53031
1:35
Bell AH-1G Cobra (late production), US Attack Helicopter (100% new molds) USUALLY é£59.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£39.87
-
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 ICMDS3512
WWII Red Army Rocket Artillery (BM-13-16 on W.O.T. 8 chassis, Model W.O.T. 6, WWII Soviet BM-13-16 MLRS Vehicle Crew, RKKA Drivers (1943-1945)) WAS é£73.80. TEMPORARILY SAVE 1/3RD!!! DISCONTINUED DECEMBER 2023
Military vehicle kits (injection)
Special Offers
£49.20
-
In Stock
5+

   1   |   2      »      [2]

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