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=7939 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 41 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
Begemot BT48057
1:48
Sukhoi Su-30SM Flankers from Irkutsk. Decal pack with options to make all known Su-30SMs - prototypes and all Su-30SMs from the Russian Air Force, Armenian AF, Belarussian AF, Kazakh AF, Su-30MKI form Indian AF, Su-30MKA from Algeria AF, Su-30MKM from Malazian AF and Su-30SME form Myanmar AF. Including six big size decals sheet, 13-pages big size instruction.
Aircraft decals (military)
Catalogue
£23.40
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
Begemot BT48032
1:48
Grumman F9F Panther Decal with opportunity make 12 marking variations of F9F Panther from US NAVY and MARINES aviation and Argentinian naval aviation. In decal consist full stencil set for one aircraft. Including two big size decals sheet and 4-pages big size instruction. [F9F-2 F9F-2B F9F-3 F9F-5 F9F-5KD]
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
14

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

Begemot BT48025
Re-printed! Yakovlev Yak-3 family. Decal set consist from 4 big decal sheets andchoice of 68 versions this best Soviet fighter of the WWII in Red Army AF, Soviet AF, Yugoslavia, France and Poland color schemes.
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

Begemot BT48041
Re-printed! Petlyakov Pe-2 family. 108 markings for different modification Pe-2 family aircrafts from VVS RKKA, Finland, Yugoslavian, Poland, Bulgarian and Hungary Air Forces.
Aircraft decals (military)
Catalogue
£21.40
-
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

Begemot BT48005
Re-printed! Sukhoi Su-27 Flanker family Part 2 (24) Variety of camouflage schemes operated by Russia; Vietnam; Belarus; Ukrania; Indonesia; Kazakstan; Ethiopia; Single and Two Seaters, Aerobatic Team etc. Inc large paint mask
Aircraft decals (military)
Catalogue
£10.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
Begemot BT48056
Registration numbers, all this type 2018. Including six big size decals and 1-pages A4-size instruction.
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
15

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
Begemot BT48055
Lavochkin La-9/La-11 Decal with opportunity make 29 marking variations of La-9 and La-11 family aircraft's all types, from next AF's: USSR, China, North Korea, Romania and Indonesia. 4 sets of full stencil for La-9(11) aircraft included. Including two big size decals and 5-pages instruction A3-size instruction.
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
7

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

Begemot BT48008
Re-printed! Sukhoi Su-27 Family 'Demo-Flankers'. Includes 2 big decal sheet and 1 small decal sheet for 45 aircraft and 20 page A4 instruction booklet
Aircraft decals (military)
Catalogue
£14.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

Begemot BT48039
Re-printed! Sukhoi Su-35S new generation of the Flankerƒ¯é¿é½
Aircraft decals (military)
Catalogue
£19.40
-
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

Begemot BT48040
1:48
Re-printed! Mikoyan MiG-31BM/BSM Foxhound This decal specially designed for the Avant Garde Model kit. Decal set consist from 2 big and 1 medium sheets and include 15 versions of various aircrafts and full stencils for aircraft and weapons.
Aircraft decals (military)
Catalogue
£16.99
-
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

Begemot BT48044
Re-printed! Mikoyan MiG-31 'Foxhounds' (standard versions NOT MiG-31 BM or BSM). Decal set consist from 4 big and one medium decal sheets and include 54 versions of all 'Foxhounds'
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
6

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

Begemot BT48045
1:48
Re-printed! Sukhoi Su-34 Decal with opportunity make all known at now days Su-34s - prototypes and all Su-34s from the Russian Air Force, include aircraft from RuAF group in Syria with mission markings. Full stencil set for one aircraft and fill complect of external armament. Including four big size decals sheet, two small size decal sheet, 8-pages big size instruction and 1 pages small size instruction.
Aircraft decals (military)
Catalogue
£22.40
-
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

Begemot BT48051
Re-printed! Sukhoi Su-33 from Soviet Naval Aviation in Syria. 10 markings scheme - any Su-33s aircraft, which took a part in Admiral Kuznetsov carrier mission in Syria. Decal includes insignias, board numbers, badges and mission marks. This set DOES NOT include stencil for aircraft and external armament, please use BT4809 "Su-27 family full stencil". Including one big size decals and two-pages instruction.
Aircraft decals (military)
Catalogue
£10.70
-
In Stock
6

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
Begemot BT48052
Yakovlev Yak-130 Decal with opportunity make all known at now days Yak-130s - from the Russian, Belarus, Algeria, Myanmar, Laos and Bangladesh Air Force. Full stencil set for one aircraft and full complect of external armament. Including three big size decals sheet, one small size decal sheet and 10-pages big size instruction. http://www.begemotdecals.ru/doc/48-052%20Yak-130%20ins%202.pdf http://www.begemotdecals.ru/doc...
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
9

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
Begemot BT48059
MiG pink sealant. imitation of pink supersonic sealant, which use in Mikoyan MiG-25, Mikoyan MiG-31, sometimes in other types of aircraft. Three different thickness of lines, two shades of pink colours. Including one standard size decal sheet, there is no instruction.
Aircraft decals (military)
Catalogue
£5.60
-
In Stock
7

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

Begemot BT48010
1:48
Mikoyan MiG-25 Foxbat. Markings for 23 aircraft from USSR, Algerian Air Force; Syrian Air Force; Libyan Air Force; Iraq Air Force; Ukranian Air Force; Bulgarian Air Force; Indian Air Force
Aircraft decals (military)
Catalogue
£10.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

Begemot BT48014
1:48
Mikoyan MiG-29SMT. Decal for all known MiG-29SMTs (9.19 and 9.19P) from Algerian and Russian Air Force, include MiG-29SMTs from Russian Air and Space Force Group in Syria . Set include full stencil for one aircraft and for external armament.
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

Begemot BT48046
Bell P-63 Kingcobra (this is an old, unused part number but it is a new decal) includes 51 marking variations of P-63 Kingcobra aircraft from next AF's: USAF, RAF, VVS RKKA, French and Honduras Air Forces. Including three big size decals and 10-pages big size instruction.
Aircraft decals (military)
Catalogue
£19.40
-
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

Begemot BT48002
1:48
Mikoyan MiG-21 Fishbed (18) Red 23 32 Guards Fighter Regt OR in Cuban markings; Red 415 N. Korean Air Force 1967; 10th FS Syrian Air Force 1967; 959th Training Regt, Soviet Air Force; Red 1851 JG 8 E German Air Force; Red 257 31 IAP Soviet Air Force; Red 4326 921st F Regt N Vietnamese Air Force; Redv6122 Vietnamese Air Force; No 54 1967 Soviet display team; Blue 03 136th FB Regt Soviet Air Force Afghistan; 1st FS Croatian Air ...
Aircraft decals (military)
Catalogue
£7.30
-
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

Begemot BT48037
Sukhoi Su-24M Russian aircraft in Syria
Aircraft decals (military)
Catalogue
£10.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

Begemot BT48050
1:48
USSR Air Force insignia, type 1955. Decal set with seven different sizes of USSR Air Force insignia (type 1955), for all main types of RuAF aircrafts and helicopters. Including three big size decals and 2-pages A3-size instruction.
Aircraft decals (military)
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

Begemot BT48047
1:48
"Additional insignias of Soviet Naval Aviation, type 2010". Decal set with different sizes of - MA VMF ROSSII and registration codes writing, for all main types of aircrafts and helicopters.
Aircraft decals (military)
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

Begemot BT48004
Re-printed! Sukhoi Su-27 Flanker family Part 1. (22) Variety of camouflage schemes operated by Russia; Uzbekistan; India `Hunting Hawks' Team; China; etc. ; Single and Two Seaters, Aerobatic Team etc. Inc large paint mask
Aircraft decals (military)
Catalogue
£10.70
-
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

Begemot BT48024
1:48
Re-printed! Sukhoi Su-15_Flagon (14) wide choice of aircraft in Soviet and Ukraine service most in natural metal finish with two in brown/green/lt blue camouflage Includes stencil data.
Aircraft decals (military)
Catalogue
£8.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

Begemot BT48017
1:48
Mikoyan MiG-29 family full stencil data. Full stencils for MiG-29s in standard grey-green painting scheme and for 4-tone camouflage scheme. Also included full stencil for wing pylons and any types of external weapons.
Aircraft decals (military)
Catalogue
£8.99
-
In Stock
13

   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