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 = 955 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 156 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 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 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 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 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 ICM48080
RAF Pilots in Tropical Uniforms (1941-1945) (100% new molds) On the eve of the Second World War, the Royal Air Force was organizationally divided into Nome Command and Overseas Command. The Overseas Command, in turn, included the Air Forces of the Middle East, Palestine, Transjordan, Iraq, Aden, the Mediterranean, India, the Far East, as well as wings in Egypt and Sudan. The hot climate zones required a uniform adapted to the...
Figures (injection)
Catalogue
£11.20
£10.08
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 ICM48196
Mitsubishi Ki-21-Ia 'Sally', Japanese Heavy Bomber *new parts The Ki-21 pre-production bomber made its first flight in late 1936. Serial production of its first modification, the Ki- 21-Ia, began in 1938, the same year the aircraft made its combat debut. Air units of the Imperial Japanese Army, which were equipped with the Ki-21-Ia, were involved in active combat operations in China. In the spring of 1939, the Ki-21- Ia took p...
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 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 ICM48224
Gotha Go-244B-2 WWII German Transport Aircraft *new parts. Difficulties encountered in the use of cargo gliders and a shortage of aircraft to tow them led to the creation of a motorised version of the Go 242, the Go 244 transport aircraft. These medium-sized transport aircraft were converted Gotha Go 242 airframes of various modifications. Aircraft engines were installed in front of the tail beams, fuel tanks were mounted insi...
Aircraft kits (injection)
Catalogue
£55.80
£50.22
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 ICM48313
Bristol Beaufort Mk.IA with RAF pilots. The Bristol Beaufort torpedo bomber began to arrive at the RAF Coastal Command in late 1939, and the following spring they began to see combat. The aircraft carried out aerial mining of sea harbors, conducted day and night torpedo attacks, and bombed coastal targets. In the course of its operation, the Bristol Beaufort was improved, its armament and equipment changed. The Mk.IA modificat...
Aircraft kits (injection)
Catalogue
£58.80
£52.92
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 ICM48195
Mitsubishi Ki-21-Ib "Sally" Japanese Heavy Bomber (100% new molds) The Ki-21 bomber, also known as the 'Sally' during World War II, was developed by Mitsubishi designers in accordance with the requirements set by the General Aviation Directorate. The first prototype of the aircraft took off on December 18, 1936, and flight tests of prototypes took place over the next year. Serial production began in the spring of 1938. The bom...
Aircraft kits (injection)
Catalogue
£59.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 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 ICM3042
1:32, 1:35, 1:48
WWI British 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 ICM3035
1:32, 1:35, 1:48
Personal protective 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 ICM3038
1:32, 1:35
Wehrmacht trucks Acrylic paint set x 6 12ml bottles (designed 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 ICM3037
1:32, 1:35, 1:48
WWII US Infantry Acrylic paint set x 6 12ml bottles [WWII US Military Patrol]
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 ICM3034
1:32, 1:35, 1:48
American Civil War. Confederate infantry Acrylic paint set x 6 12ml bottles[Fierce battle.]
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 ICM3036
1:32, 1:35, 1:48
Armament for WWII German Aircraft Acrylic paint set x 6 12ml bottles
Paint, Acrylic 12ml
Catalogue
£7.40
-
In Stock
10+

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

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