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=362992 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 13 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
Exito Decals EXED48013
Eastern Front Fighters vol.2 - Yak-1b - Bf-110 - Bf-109 Our thirteenth decal set is entitled "Eastern Front Fighters vol.2" and contains markings for three different fighter aircraft operating in the East during World War II: - Yakovlev Yak-1b, coded 'Red 22' of 562nd IAP / 318th IAD PVO, Moscow area, USSR, 1943. - Messerschmitt Bf-110E-2 trop, WNr. 4018, coded S9+GP, flown by Oblt. Gƒ¼nther Tonne, Kapitƒ¤n of 6./SKG 21...
Aircraft decals (military)
Catalogue
£13.70
-
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
Exito Decals EXED48014
Wulf Pack vol.2 - Fw-190 Our fourteenth decal set is entitled "Wulf Pack vol.2" and covers three different variants of the famous 'Butcher Bird': - Focke-Wulf Fw-190A-5, WNr. 2648, 'Yellow 3', flown by Lt. Josef Wurmheller, Kapitƒ¤n of 9./JG 2, Vannes, France, summer 1943. - Focke-Wulf Fw-190A-8, WNr. 737938, 'Blue 9' of 4./JG 301, Germany, spring 1945. - Focke-Wulf Fw-190D-9, WNr. 210129, 'Red 9' of 12./JG 54, Fr...
Aircraft decals (military)
Catalogue
£14.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
Exito Decals EXED48011
' Pacific Warriors vol.1' and includes markings for three US fighter aircraft operating in the PTO during World War II: - North-American P-51D-25-NA, Mustang s/n 44-72640, coded '501', flown by Capt. Evelyn Neff of 457th FS / 506th FG, Iwo Jima, late spring / summer 1945. - Curtiss P-40N Warhawk, s/n unknown, coded '47' and named 'Mary Lou', flown by Lt. David R. Winternitz of 8th FS / 49th FG, New Guinea, after February 1...
Aircraft decals (military)
Catalogue
£13.70
-
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

No Image
Exito Decals EXED48012
' In Enemy Hands' and covers three different Luftwaffe aircraft captured and repainted by Western Allies: - Messerschmitt Bf-109K-4, coded 'Yellow 5', Salzburg-Maxglan, Austria, mid-1945. - Heinkel He-111H-6, coded 'HS-?' and named 'Delta Lily', No. 211 Group RAF, North Africa, December 1942. - Junkers Ju-88A-4, WNr. 300227, 86th FS / 79th FG, Foggia, Italy, autumn 1943. None of these marking options have previously be...
Aircraft decals (military)
Catalogue
£16.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

No Image
Exito Decals EXED48009
Lockheed P-38F/G Lightning - "Lightning Strike" Our ninth decal set is entitled 'Lightning Strike' and includes markings for three P-38s: - P-38G-13-LO, s/n 43-2204, 'Beautiful Lass', flown by Lt. John G. 'Jump' O'Neill of 9th FS / 49th FG, Dobodura, New Guinea, late October 1943. - P-38F-1-LO, s/n 41-7578, 'Marjorie Ann', possibly flown by Maj. Richard Ira 'Dick' Bong during his war bond tours, USA, late summer 1944. - ...
Aircraft decals (military)
Catalogue
£13.70
-
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
Exito Decals EXED48010
Messerschmitt Bf-109 - "Eastern Front Fighters" Our 10th decal set is entitled 'Eastern Front Fighters' and covers three different Messerschmitts flown by notable Luftwaffe aces in the East: - Messerschmitt Bf-109E-7, 'White 2', flown by Lt. Heinrich Ehrler of 4./JG 5, Alakurtti, Finland, late April 1942. - Messerschmitt Bf-109F-4 trop, WNr. 10266, flown by Maj. Gordon Gollob, Kommodore of JG 77, Oktoberfeld, Crimea, Sov...
Aircraft decals (military)
Catalogue
£11.50
-
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

Exito Decals EXED48007
'Yak Attack' and includes markings for three striking Yak-1b fighters: - Yakovlev Yak-1b flown by Capt. Vladimir P. Pokrovskiy of 2nd GIAP / 6th IAD, Air Force of the Northern Fleet, probably late 1943. - Yakovlev Yak-1b, coded 'White 42', flown by Snr. Lt. Garri A. Merkviladze of 152nd GIAP / 12th GIAD, 1st Ukrainian Front, Germany, spring 1945. - Yakovlev Yak-1b, most likely coded 'White 20', flown by Snr. Lt. Fotiy...
Aircraft decals (military)
Catalogue
£11.50
-
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

Exito Decals EXED48008
Yoxford Girls - North-American P-51D Mustang P-51D Mustang obviously became one of the most popular modelling subjects of the last few months. We decided to join the trend with our eighth sheet entitled 'Yoxford Girls' , which covers three P-51Ds from the famous 357th Fighter Group, all featuring some sexy nose art: - P-51D-5-NA, s/n 44-13712, flown by Capt. Robert W. Foy of 363rd FS / 357th FG, Leiston, UK, late July-ea...
Aircraft decals (military)
Catalogue
£11.50
-
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

Exito Decals EXED48005
1:48
"Sweet Fourteens" - Supermarine Spitfire Mk. XIVe - Spitfire FR Mk.XIVe (TZ112), coded DN-Y of No. 416 Sqn RCAF, Uetersen, Germany, December 1945-January 1946. - Spitfire FR Mk.XIVe (TZ198), coded 2I-M of No. 443 Sqn RCAF, Uetersen, Germany, January 1946. - Spitfire FR Mk.XIVe (SM937), coded 62 of No. 151 OTU, Peshawar or Ambala, British India, 1946. None of these marking options have ever been reproduced in decal form. ...
Aircraft decals (military)
Catalogue
£13.70
-
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

Exito Decals EXED48006
The Last in Line vol.1 - Messerschmitt Bf-109G-10 WNF Entitled 'The Last in Line vol.1' , this decal set is the first of a series devoted to the German aircraft produced in the last months of World War II. This release covers three Bf-109G-10s manufactured by WNF, including one machine used in the postwar Yugoslavia: - Messerschmitt Bf-109G-10/U4, W.Nr. 61xxxx, coded 'Black 5', flown by Oblt. Fritz Nƒ¤chster of 5./JG 52, V...
Aircraft decals (military)
Catalogue
£11.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

Exito Decals EXED48003
' Sexy Spitfires' and includes markings for three aircraft: - Supermarine Spitfire LF Mk.Vb (EN921), coded YO-A of No. 401 Sqn RCAF, flown by F/O Jack Sheppard, Redhill / Staplehurst / Biggin Hill, UK, July-October 1943. - Supermarine Spitfire LF Mk.VIII (MT841), coded U of No. 2 Sqn RIAF, Kohat, British India, spring 1946. - Supermarine Spitfire LF Mk.IXe (TA864), coded LW-L of No. 318 (Gdanski) Sqn RAF, flown by P/O Zdzis...
Aircraft decals (military)
Catalogue
£13.70
-
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

Exito Decals EXED48001
1:48
Messerschmitt Bf-109G-6, 'White 12', flown by Uffz. Anton Riemer of 7./JG 77, Mizil, Romania, January 1944. Messerschmitt Bf-109G-6, 'Black 5', 2./JG 53, Borovo, Croatia, May 1944. Messerschmitt Bf-109G-6, W.Nr. 166133; flown by Capt. Constantin 'BƒÆ'é¢zu' Cantacuzino, San Giovanni near Foggia, Italy, 27th August 1944. The first two options have never been reproduced in decal form before, whilst the last one have not recei...
Aircraft decals (military)
Catalogue
£10.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

Exito Decals EXED48002
1:48
Focke-Wulf Fw-190A-3, 'Yellow 7', flown by Oblt. Detlev Rohwer, Kapitƒ¤n of 6./JG 1, Woensdrecht, the Netherlands, late spring 1942. Focke-Wulf Fw-190A-4, W.Nr. 0799, coded 'SK+OU', probably belonging to I./SG 101, France, 1943. Focke-Wulf Fw-190A-5, 'Yellow 5', flown by Fw. Karl 'Charly' Willius of 3./JG 26, Dno, Soviet Union, early April 1943. None of these aircraft have been available in decal form before. 'Yellow 5' ...
Aircraft decals (military)
Catalogue
£10.99
-
In Stock
5+


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