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=8037 AND ps.id = 89514 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 20 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
Guideline Publications SAMMCOMP03
Colour Conundrum Compendium No.3 84 pages By Paul Lucas Guideline Publications is pleased to announce the third 'Colour Conundrum Compendium'. This volume collates two groups of articles, the first dealing with the RAF's 'exotic' Tropical Land and Sea Schemes and colours from 1933-1945, while the second deals with the unusual RAF Photographic Reconnaissance Schemes and colours from 1939-1945. As with Compendium No.2, thi...
Aircraft books
Catalogue
£23.00
£20.70
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
Guideline Publications SAMMCOMP02
Colour Conundrum Compendium No.2 (including previously published material and updates) By Paul Lucas Colour Art work by Mark Rolfe and Jan Polc Author Paul Lucas Guideline Publications is pleased to announce the second 'Colour Conundrum Compendium'. With this further release the Author focuses on three themes: the RAF in the Second World War, the Post War Fleet Air Arm, and related products of the United States' avi...
Aircraft books
Catalogue
£23.00
£20.70
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
Guideline Publications SAMMCOMP01
Colour Conundrum authored by Paul Lucas with updated content to bring this right up to date. 84 pages.
Aircraft books
Catalogue
£19.99
£17.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

Guideline Publications GP565181
Camouflage & Markings Normandy Campaign Part 2: U.S. and German forces volume 3 written and compiled by Mark Healey
Military vehicle books
Catalogue
£16.00
-
In Stock
1

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

Guideline Publications WPA01
Warpaint Armour no 1 - Armour on the Eastern Front 1941 - 1945. 84 pages Perfect bound Warpaint Armour is an exciting new series by Guideline License Publications. Each title will cover an important conflict or period, looking at the tanks and other armoured fighting vehicles that shaped pivotal moments in the history of modern warfare. Illustrated throughout with archival images and specially commissioned colour profil...
Aircraft books
Catalogue
£18.00
-
In Stock
3

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications SAMIP02
No Scale
Aircraft in Profile US Navy and Air Force Vol 1 issue 2 Aircraft in Profile is a monthly section occupying the centre pages of Scale Aircraft Modelling magazine providing an initial 'first step' in researching an aircraft type, and is aimed at those readers who have had little previous interest in its subject, rather than those with expert knowledge. The section outlines the history and development of its chosen type providin...
Aircraft books
Catalogue
£11.99
-
In Stock
5

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications AIP01
No Scale
Armour in Profile-Armoured Fighting Vehicles USA 1945-2018 By MP Robinson, David Grunnitt, Leif Robinson Armour in Profile: Armoured Fighting Vehicles of the United States Army, 1945-2018 contains profiles of five armoured fighting vehicles that have shaped the strategy and tactics of the United States Army since the end of World War II. From the battlefields of the Korean Peninsula and the jungles of Vietnam, to the plains ...
Military vehicle books
Catalogue
£11.99
-
In Stock
4

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications SAMIP01
No Scale
Aircraft in Profile - British Classics Volume 1 Issue 1 . By Gary Hatcher Aircraft in Profile is a monthly section occupying the centre pages of Scale Aircraft Modelling magazine providing an initial 'first step' in researching an aircraft type, and is aimed at those readers who have had little previous interest in its subject, rather than those with expert knowledge. The section outlines the history and development of its ch...
Aircraft books
Catalogue
£11.99
-
In Stock
2

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications GP565310
Airlift Force RAF Transport Command 1948-1967 By Colin Ovens Consider, for a moment, these three Royal Air Force Command titles:- "Royal Air Force Bomber Command"- a name that, for many, may generate imposing images of Wellingtons, Halifaxs, Stirlings, Lancasters, Mosquitos, Lincolns, BAC/EE Canberra s, and the V-Bombers; "Royal Air Force Fighter Command"- a Command title that readily conjures up exciting images...
Aircraft books
Catalogue
£19.00
-
In Stock
3

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications GP565198
Camouflage & Markings. Armour in Theatre No 4 - Tanks in the Great War 1914-1918. No 4 of this ongoing series of Armour in Theatre examines the development and employment of the first tanks. Toe volume covets the antiriot of the Great War, the evolution of the tank with a special and understandable emphasis on the developments with Great Britain. Special treatment is given to two important tank battles of the conflict - those ...
Military vehicle books
Catalogue
£16.00
-
In Stock
3

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications GPSAMCC9
Combat Colours No.9 - Nicholas Millman Mitsubishi Zero - Type 0 Carrier Fighter (A6M) 'Zeke' in World War Two. No.9 in the series charts the colour schemes and markings worn by this iconic Japanese naval fighter which saw service throughout the whole of the Pacific War from 1940 in China, to the final defence of the homeland in 1945. Structured chronologically by variant, it covers the development from the A6M1 to A6M7/8, incl...
Aircraft books
Catalogue
£16.00
-
In Stock
1

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

Guideline Publications GP565167
Camouflage & Markings Volume 1: Armour in Theatre Eastern Front Spring and Summer 1943 [GP565167]
Military vehicle books
Catalogue
£9.99
-
In Stock
4

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications GP565174
Camouflage & Markings Volume 2: Armour in Theatre Normandy Campaign Part 1: British and Canadian Forces
Military vehicle books
Catalogue
£16.00
-
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

Guideline Publications GPSAM02
Camouflage & Markings 2: The Battle For Britain-RAF May to December 1940
Aircraft books
Catalogue
£14.95
-
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

Guideline Publications GPSAM05
No Scale
Camouflage & Markings 5: RAF Fighters 1945-1950 Overseas Base
Aircraft books
Catalogue
£17.95
-
In Stock
3

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications GPSAMCC3
No Scale
Combat Colours 3: The Curtiss P-36 and P-40 in USAAC/USAAF service 1939-1945 The usual detailed 'Scale Aircraft Modelling' treatment of the plane which kept Curtiss at the forefront of fighter design following the success of their biplane line. When re-engined with Allisons the P-36 became the P-40 and served with distinction throughout World War II. 8 colour 3-views. 150 colour profiles. 44 black/white photos.
Aircraft books
Catalogue
£9.95
-
In Stock
4

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications GPSAMCC4
Combat Colours 4: Pearl Harbor and beyond- December 1941-May 1942 This title depicts the camouflage and markings of a whole host of types belonging to the nations dragged into a Pacific War following the Japanese attack on Pearl Harbor. As usually a huge amount of well researched information is presented for the modeller and historian. 215 colour profiles. 27 black/white photos.
Aircraft books
Catalogue
£11.95
-
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

Guideline Publications GPSAM06
Camouflage & Markings No.6. The Air Campaign for the freedom of Libya Febuary to October 2011. Operations Odyssey Dawn and Unified Protector
Aircraft books
Catalogue
£17.99
-
In Stock
5

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Guideline Publications GPSAMCC8
No Scale
Combat Colours No.8 - An illustrated guide to the colour schemes and markings of the Supermarine Spitfire in WWII volume 1: Merlin-Engined Marks (Mk.I to Mk.XVI)
Aircraft books
Catalogue
£15.00
-
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

Guideline Publications GPSAMCC7
No Scale
Combat Colours No.7 Focke-Wulf Fw-190 in WWII
Aircraft books
Catalogue
£15.00
-
In Stock
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