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 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 169 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

Syhart Decal SY144074
Mikoyan MiG-21MF 7701 "Splinter Camouflage" Czech Air Force 1998
Aircraft decals (military)
Catalogue
£4.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 SY144146
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
£6.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 SY32146
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
£13.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

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 SY72135
McDonnell F-4F Phantom II 37+16 & Panavia Tornado IDS 45+03 "50 Jahre WTD-61" 2007 This decals set contains all markings to complete two aircrafts Faced with the need to evaluate the aircraft of the future Luftwaffe, Wehrtechnische Dienststelle 61 (WTD 61) , literally "military technical center" was created in 1957. It is the dedicated flight test and development center for all German military aircraft, based in Manching...
Aircraft decals and paint masks (military)
Catalogue
£13.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 SY72137
1:72
Eurofighter Typhoon 30+09 "Bronze Tiger - Tigermeet 2014" TaktLwG 74 In 2013, the JBG-32 squadron based in Lechfeld, equipped with Tornado ECR was disbanded, the Tornado ECRs were sent to the AG-51 "Immelmann" reconnaissance unit in Schleswig already equipped with Tornado IDS. But the dissolution of JBG-32 leads to the disappearance of squadrons 321 "Tigers" and 322 "Monsters". The 321 Tigers being a member of the NATO Tig...
Aircraft decals and paint masks (military)
Catalogue
£14.00
-
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 SY72138
Dassault Rafale C 118-EF "Tigermeet 2012" EC 5/330 "Cƒ'te d'Argent" Full member of the NATO Tiger association since 1986, the ECE 5/330 "Cƒ'te d'Argent" is one of few French Air Force squadrons to be equipped with several aircraft types (Mirage 2000, Rafale...). The 2012 edition of the NATO Tigermeet took place on Orland Airbase in Norway, the event is named "Arctic Tiger". After two tigermeet without any Rafale (2010 an...
Aircraft decals and paint masks (military)
Catalogue
£12.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

No Image
Syhart Decal SY72143
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
£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

No Image
Syhart Decal SY72146
1:72
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
£8.00
£7.20
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 SYA001-32
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 decals (military)
Catalogue
£8.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 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 SYA001-72
1:32
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 (resin)
Catalogue
£3.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 SYA002-72
1:72
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
£8.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 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 SY72144
General-Dynamics F-16A Falcon J-364 "Solo Display 1997-1998" Royal Netherlands A.F
Aircraft decals (military)
Catalogue
£11.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 SY72145
Lockheed-Martin F-16AM Falcon J-016 "Solo Display 2001-2004" Royal Netherlands A.F
Aircraft decals (military)
Catalogue
£12.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 SY72141
Dassault Rafale C 30-GK "80 years EC 3/30 Lorraine" 2021
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

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

   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