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=362458 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 72 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
Syhart Decal SY48143
Dassault Rafale C 4-GR "Rafale Solo Display 2021" From July 2020, the "French Air Force" becomes the "French Air and Space Force" , changing at the same time its logo, where the traditional "hawk" now flies over the curves of the Earth. The Rafale "Solo Display 2021" is the first demonstrator to wear the colors under this new designation, with the new logo finely displayed in the curves of the lower surface. The decora...
Aircraft decals and paint masks (military)
Catalogue
£20.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

No Image
Syhart Decal SY48146
Boeing F/A-18F Super Hornet 166673 "Vandy One" VX-9 - US Navy 2023 Since the 1950s, VX-4 was the U.S. Navy's Air Test and Evaluation Squadron Four, testing and evaluating weapons and equipment, and developing tactical procedures for other Navy squadrons. Late 1960s, during the Vietnam War, the VX-4 based at NAS Point Mugu (CA) was testing night operating procedures, so some of their F-4 Phantoms received for first time an ...
Aircraft decals and paint masks (military)
Catalogue
£10.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

No Image
Syhart Decal SYA001-48
Smokewinder. 1x pair- resin) Smoke pod for demonstrators. This kit consists of two resin parts printed in very high resolution 3D and an A6 instruction page allowing a simple placement of a pair of ƒšé«smokewindersƒšé» used on many aircrafts for aerial demonstrations. Smokewinders are used as smoke generators on many types of aircrafts around the world. Placed on wingtips or under wings, F-16, Rafale, Gripen, Eurofighter, ...
Aircraft detailing sets (decal and resin)
Catalogue
£5.00
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
Syhart Decal SYA002-48
2000L fuel tanks for Mirage 2000 (1x pair - resin) (RPL541/542) for Dassault-Mirage 2000 (all french versions and few export versions). This kit consists of four very high resolution 3D-Printed resin parts and an A5 instruction page allowing simple assembly of a pair of 2000 liter tanks for Mirage 2000 (RPL541 & RPL542). The RPL541 & 542 tanks (Releasable underwings Tanks) are used on all versions of French Mirage 2000s ...
Aircraft detailing sets (resin)
Catalogue
£10.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

No Image
Syhart Decal SY48144
General-Dynamics F-16A Falcon J-364 "Solo Display 1997-1998" Royal Netherlands A.F
Aircraft decals (military)
Catalogue
£14.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

No Image
Syhart Decal SY48145
Lockheed-Martin F-16AM Falcon J-016 "Solo Display 2001-2004" Royal Netherlands A.F
Aircraft decals (military)
Catalogue
£15.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

No Image
Syhart Decal SY48141
Dassault Rafale C 30-GK "80 years EC 3/30 Lorraine" 2021
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

No Image
Syhart Decal SY48142
Dassault Rafale C 4-IM "10 years Rafale Solo Display" - 2019/2020
Aircraft decals and paint masks (military)
Catalogue
£19.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
Syhart Decal SY48920
Dassault Rafale "Export versions" (part 2) - Greece - "Hellenic Air Force"
Aircraft decals (military)
Catalogue
£10.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

No Image
Syhart Decal SY48132
Dassault Rafale C "100 years SPA 93, 97, 91" RC 2/30 Normandie-Niemen - 2017.The "Normandie-Niemen" is one of the most famous squadrons of the French Air and Space Force, memoring the historical traditions of the French pilots who joined Russia during the World War II to form this FAFL* squadron from 1942 within the Russian Air Force, like the other FAFL squadrons formed within the Royal Air Force since 1940. (*FAFL: Free Fren...
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

No Image
Syhart Decal SY48133
Dassault Rafale C 30-GT "75 ans retour en France du Normandie-Niemen" RC 2/30 " 2020 The "Normandie-Niemen" is one of the most famous squadrons of the French Air and Space Force, memoring the historical traditions of the French pilots who joined Russia during the World War II to form this FAFL* squadron from 1942 within the Russian Air Force, like the other FAFL squadrons formed within the Royal Air Force since 1940. (*FAFL: ...
Aircraft decals (military)
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

No Image
Syhart Decal SY48130
Dassault Rafale C 4-GJ "100 years SPA160 - Diable" ETR 3/4 Aquitaine 2018 The SPA 160 "Diable Rouge" (Red devil) was created in 1918, 100 years later, this squadron flies on the Rafales of the ETR 3/4 "Aquitaine" on BA113 Saint-Dizier, after being reborn several times: - between 1939 and 1940 on Curtiss H-75A, - in 1950 on Vampire of the squadron EC 3/4 "Flandres" - in 1986 on Mirage IIIBE of the squadron EC 1/13 "A...
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

No Image
Syhart Decal SY48131
1:48
Dassault Rafale B 4-IA "100 years SPA167 - Cigogne de Romanet" EC 2/4 La Fayette 2018 - Limited edition - The SPA 167 "Cigogne de Romanet" was created in 1918, and from 1942 it will become one of the permanent badges of the "La Fayette" Squadron, with its sister, the N 124 "TƒÆ'éªte de Sioux" badge. One hundred years after its creation, it has flown on many aircrafts (P-40, P-47, Vampire, F-84F, Mirage III, Mirage 2000N)...
Aircraft decals (military)
Catalogue
£10.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
Syhart Decal SY48134
Dassault Rafale C 7-HR & 7-GM "10 years in United Arab Emirates" EC 1/7 Provence 2020 - Limited edition The BA104 is a part of United Arab Emirates base of Al-Dhafra intended for the French military detachment, it's a multination airbase where the majority of the air forces of the UAE is based, but also a large American military detachment. Located on a strategic place in the Persian gulf, the BA104 ensures the security in ...
Aircraft decals (military)
Catalogue
£10.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
Syhart Decal SY48125
Dassault Rafale M "100 ans Flottille 11F" 2019. The Flottille 11F celebrated its 100th anniversary in September 2019, inheriting the traditions of the B102 Squadron created in 1919. For this centenary it is not less than five Rafale M who received special tails: - The right side is identical on all five aircrafts, with the colors of 100th anniversary of the flottille 11F - The left sides are different for each aircraft, ...
Aircraft decals (military)
Catalogue
£12.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

No Image
Syhart Decal SY48126
1:48
Dassault Rafale M #44 "Celtix Tiger - Tigermeet 2018" Flottille 11F The edition 2018 of NATO Tigermeet was held in May 2018 on the Poznaƒ...aāé-z air base in Poland. For the event, the Flottille 11F based on the Landivisiau NAS in Brittany specially decorated the Rafale M #44 with the colors of the tigers with the theme of the famous comic strip "Asterix", and its irreducible Breton village. This is how the decoration was call...
Aircraft decals (military)
Catalogue
£12.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

No Image
Syhart Decal SY48129
Dassault Rafale B 4-FP "10 years ESTA 15.004 Haute-Marne" 2017 - Limited edition - << previous reference - home - next reference >> real In December 2017, the Escadron de soutien technique aƒ©ronautique (ESTA) 15.004 "Haute Marne" (aeronautic technical support squadron) based on BA113 Saint-Dizier celebrated its 10th anniversary. For the event, the Rafale B nƒšé°349 4-FP received a special tail, designed by Rƒ©gis ...
Aircraft decals (military)
Catalogue
£11.99
-
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

No Image
Syhart Decal SY48115
Dassault Rafale C 4-GL "Rafale Solo Display 2017"
Aircraft decals (military)
Catalogue
£13.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

No Image
Syhart Decal SY48117
Dassaut Rafale M "Tigermeet 2017" Flottille 11F
Aircraft decals (military)
Catalogue
£14.99
-
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

No Image
Syhart Decal SY48122
1:48
2x Dassault-Mirage IIIE "Regent Papa" 70 years EC2/4 La Fayette" 1916-1986. In 1985 and 1986, the "Regent Papa" team, flown with two Mirage IIIE from the EC2/4 "La Fayette" squadron displayed tactical demonstrations through airshows. On October 4, 1986 at the Luxeuil airbase, the 4th wing celebrated the 150,000 flight hours on Mirage IIIE, while the fighter squadron 2/4 "La Fayette" celebrated its 70th anniversary. For t...
Aircraft decals (military)
Catalogue
£10.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
Syhart Decal SY48915S
Dassault Rafale B/C/M "Standard stencils" (French versions) this decals set contains the "stencils only" allowing you to make other French Rafales, as long as you have registrations and badges left, without needing to get other complete Syhart SY48915V2 decals sets.
Aircraft decals (military)
Catalogue
£8.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

No Image
Syhart Decal SY48915V2
Dassault Rafale B/C/M standards "Armeĩ de l'Air & Marine"(version 2020)
Aircraft decals (military)
Catalogue
£15.99
-
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

No Image
Syhart Decal SY48918
Dassault Rafale "Export versions" (part 1) - Egypt, Qatar, India
Aircraft decals (military)
Catalogue
£14.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

No Image
Syhart Decal SY48918S
Dassault Rafale exports "Standard stencils" (English language for export versions) - This set 1/48 consists of : - 1 decals sheet - 2 colored pages of instructions (1x A4 + 1x A5) (front / back). - 1 set of masks for anti-skid zones.
Aircraft decals (military)
Catalogue
£7.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
Syhart Decal SY48119
Grumman EA-18G Growler A46-306 "100 Years 6 Squadron" RAAF - 2017. The No 6 Squadron of Royal Australian Air Force (RAAF) was formed in 1917 in England as training unit during the world war I. The squadron re-raised during World war II as maritime patrol squadron. Then from 1948, become part of No 82 Wing, as bomber operational conversion unit, since that based on RAAF base Amberley in Queensland. Along it's history, the...
Aircraft decals (military)
Catalogue
£12.99
Out of Stock
0

   1   |   2   |   3      »      [3]

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