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 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 17,088 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 113

Scale

Product Type

Manufacturer

Added Within

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
Grunau Baby IIB Mask Canopy, Fabric Flying / Control Surfaces 1/48 Precut spray masks that fit the fabric-covered, wooden construction wings and empennage of the Grunau Baby glider and using which is painting the various materials almost a piece of cake. (designed to be used with Special Hobby kits)
Aircraft paint masks (self adhesive)
Catalogue
£12.40
-
In Stock
10+

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

Portuguese Lockheed-Martin F-16A NTM 2011
Aircraft decals (military)
Catalogue
£9.50
-
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

Asian Mikoyan MiG-29 Fulcrums part 1 Malaysia, Kazakhstan, North Korea
Aircraft decals (military)
Catalogue
£10.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

Sukhoi Su-22 in Polish service part. I
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

Re-printed! Yakovlev Yak-3 Normandie-Niemen: nƒ¢Ë ...¾ 1 Cne Marcel Albert 20/06/1945, nƒ¢Ë ...¾ 5 S/Lt Roger Sauvage 20/06/1945, nƒ¢Ë ...¾ 22 Asp. Pierre Douarre 20/06/1945, Né° OO Lt-Col Louis Delfino 20/06/1945, nƒ¢Ë ...¾ 4 Lt Roger Marchi 06/1946.
Aircraft decals (military)
Catalogue
£14.60
-
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
North-American F-82F Twin Mustang 3D-Printed & coloured Interior on decal paper (designed to be used with Modelsvit kits)
3D printed decal
Catalogue
£22.60
-
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
North-American F-82G Twin Mustang 3D-Printed & coloured Interior on decal paper (designed to be used with Modelsvit kits)
3D printed decal
Catalogue
£22.60
-
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
Bristol Blenheim Mk.I 3D-Printed & coloured Interior on decal paper (designed to be used with Airfix kits)
3D printed decal
Catalogue
£21.60
-
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
1:48
North-American F-82G Twin Mustang 3D-Printed & coloured Interior on decal paper (designed to be used with Modelsvit kits) (SMALL VERSION)
3D printed decal
Catalogue
£9.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
Bristol Blenheim Mk.I 3D-Printed & coloured Interior on decal paper (designed to be used with Airfix kits) (SMALL VERSION)
3D printed decal
Catalogue
£15.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

No Image
1:48
Vought F4U Corsair Smooth Tread Mainwheels This set consists of a pair of smooth tread mainwheels, as fitted to most Birdcage Corsairs, as well as many wartime Vought F4U-1 and 1a aircraft. Accurate resin cast mainwheels with logos and tire data are finished off with well fitting, super detailed, 3D-Printed wheel hub covers. Designed for the Tamiya family of Corsairs, but can be used with any 1/48 Vought F4U Corsair kit. A si...
Aircraft wheels (resin)
Catalogue
£8.99
-
In Stock
10+

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
Vought F4U Corsair Diamond Tread Mainwheels This set consists of a pair of classic diamond tread mainwheels as fitted to many wartime Vought F4U-1 and 1a aircraft, as well as being a common fit on later model postwar Corsairs. Accurate resin cast mainwheels with logos and tire data are finished off with well fitting, super detailed, 3D-Printed wheel hub covers. Designed for the Tamiya family of Corsairs, but can be used with ...
Aircraft wheels (resin)
Catalogue
£8.99
-
In Stock
10+

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
Vought F4U Corsair Postwar Tread Mainwheels This set consists of a pair of postwar tread (circumferential ribs with side diamonds) mainwheels as fitted to many postwar Vought F4U-4, -5, and AU-1 aircraft, as well as being a common fit on -1 and -1A model postwar Corsairs. Accurate resin cast mainwheels with logos and tire data are finished off with well fitting, super detailed, 3D-Printed wheel hub covers. Designed for the Ta...
Aircraft wheels (resin)
Catalogue
£8.99
-
In Stock
10+

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
Vought F4U Corsair Stamped Hub Mainwheels This set consists of a pair of postwar circumferential tread mainwheels with one piece stamped metal wheel hubs as often fitted to later postwar Vought F4U-4, -5, and AU-1 aircraft, as well as some postwar -1 and -1a Corsairs as well. Most Vought F4U and F6F warbirds and air racers are fitted these wheels, as I suspect they stood up to time better than the cast variety. These are high...
Aircraft wheels (resin)
Catalogue
£8.99
-
In Stock
10+

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
Supermarine Spitfire Early Style Control Column This is for Supermarine Spitfire Mk Is and Vs fitted with machine guns only. If it has an eight gun A wing, this is the set for you. The spade grip features the round housing for the trigger, as well as an attached brake lever. This control column is offered in 3 versions. With and without gun camera button and a third version with the ailerons deflected. Perfect for your recent...
Aircraft detailing sets (resin)
Catalogue
£9.99
-
In Stock
10+

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
1:48
Supermarine Spitfire Late Style Control Column This stick is suitable for every Supermarine Spitfire from The Mk Vb on through the Seafire FR 47. The spade grip features the rectangular housing for the 3 position trigger switch, and a molded on brake lever. This control column is offered in 3 versions. With and without gun camera button and a third version with the ailerons deflected. A quick and easy upgrade that will be...
Aircraft detailing sets (resin)
Catalogue
£9.99
-
In Stock
10+

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
Hawker Hurricane Mk.IIc Collection (8) Hawker Hurricane Mk.IIc, BD929, 'ZY.S' of 247 (China-British) Squadron, Royal Air Force, based at RAF Exeter, UK, 1942. Hawker Hurricane Mk.IIc, serial possibly Z3574, 'OK.2' personal aircraft of Air Vice Marshal Keith R. Park, AOC Malta, Royal Air Force, based at RAF Luqa, Malta, October/November, 1942. Hawker Hurricane Mk.IIc, LF534, '6H.C' of 1688 Bomber Defence Training Flight,...
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
1:48
Hawker Hurricane Mk.IIc Trop Collection (7) Hawker Hurricane Mk.IIc Trop, LB680, 'MW.J' of 7 (Battleaxes) Squadron, Indian Air Force, based at Peshawar, Pakistan, December, 1944. Hawker Hurricane Mk.IIc, BE643, 'EG.N' of 34 Squadron, Royal Air Force, based at Palel, India 1944. Hawker Hurricane Mk.IIc 5285, 'AX.E' of 'A Flight' 11 OTU, South African Air Force based at Waterkloof, Air Base, South Africa, 1945. Hawker Hurric...
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
1:48
Hawker Sea Hurricane Mk.IIc Collection (8) Hawker Sea Hurricane Mk.IIc, NF698 '7.D' of 835 Naval Air Squadron, Fleet Air Arm, based aboard HMS Nairana, 1944. Hawker Sea Hurricane Mk.IIc NF728, 'K1.F' of 760 Naval Air Squadron, Fleet Air Arm, based aboard HMS Ravager, October 4, 1944. Hawker Sea Hurricane Mk.IIc NF694, 'U'/'Sheila' of 824 Naval Air Squadron, Fleet Air Arm, based aboard HMS Striker, 1944. Hawker Sea Hurrican...
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
Blackburn Buccaneer S.2C/D wheels and canopy frame paint masks (designed to be used with Airfix kits)
Aircraft paint masks (self adhesive)
Catalogue
£2.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

No Image
Boeing/Hughes AH-64 Apache. Seats (designed to the used with Academy and Hasegawa kits)
Aircraft seats (resin)
Catalogue
£9.40
-
In Stock
10+

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
1:48
Blackburn Buccaneer. S.2C/D Landing gears (designed be used with Airfix kits)
Aircraft detailing sets (resin)
Catalogue
£24.70
£22.23
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
Consolidated B-24 Liberator Cowlings with engines (designed to the used with Monogram kits)
Aircraft detailing sets (resin)
Catalogue
£59.35
-
In Stock
10+

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
Blackburn Buccaneer. Ejection seats x 2 (designed be used with Airfix kits) Set contains 3D-Printed parts for detailing 2 ejection seats of the aircraft models Blackburn Buccaneer (designed be used with Airfix kits)
Aircraft seats (resin)
Catalogue
£9.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

No Image
Boeing B-17G Bombardier's cockpit (Interior set) (Monogram) Set contains 3D-Printed parts for detailing of bombardier's cockpit of the aircraft models B-17G (designed to the used with Monogram kits)
Aircraft detailing sets (resin)
Catalogue
£27.99
-
In Stock
7