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=7897 AND ps.id = 956 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 89 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
Airfix AX06015A
North-American B-25C/D Mitchell The role of medium bomber proved to be one of the most crucial during the Second World War and after something of a challenging service introduction, the North-American B-25 Mitchell developed into a highly successful aircraft. Seeing service in every theatre of operation, the B-25 was the most versatile aircraft of its type, fulfilling a multitude of tactical roles in many different operating ...
Aircraft kits (injection)
Catalogue
£33.99
£30.59
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
Airfix AX04002V
Fairey Rotodyne XE-521
Aircraft kits (injection)
Catalogue
£20.49
£18.44
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
Airfix AX08019A
Vickers Wellington Mk.IA/C A08019A - Vickers Wellington Mk.IA, No. 9 Squadron Aircraft, Royal Air Force Honington, Suffolk, England, 18th December 1939. (A) A08019A - Vickers Wellington Mk.IC, Luftwaffe, formerly 311 (Czechoslovak) Squadron, 1941. (B) Making its first flight on 15th June 1936, the aircraft which took to the skies over the Weybridge factory airfield on that day was very different to the ones which entered...
Aircraft kits (injection)
Catalogue
£39.99
£35.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
Airfix AX04066
Messerschmitt Me-410A-1/U-2 & U4 Introducing the new Airfix 1/72nd scale Messerschmitt Me 410 Luftwaffe Heavy Fighter model kit, the first time this aircraft has featured in an Airfix range. Despite the fact that the Messerschmitt Me 410 is undoubtedly one of the most attractive looking aircraft of WWII, produced by one of the most famous aircraft manufacturing companies the world has ever known, the Hornisse is nowhere ne...
Aircraft kits (injection)
Catalogue
£23.99
£21.59
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
Airfix AX02007V
SR-N1 Hovercraft
Civilian vehicles kits (injection)
Catalogue
£10.99
£9.89
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
Airfix AX06019A
McDonnell-Douglas FG.1/FGR.2 Phantom RAF A THIRD DECAL OPTION HAS BEEN ADDED! A06019A - McDonnell Douglas Phantom FGR.2, No. 56 Squadron, Royal Air Force Wattisham, Suffolk, East Anglia, England 1991. (A) A06019A - McDonnell Douglas Phantom FG.1, Aircraft flown by Wing Commander John Brady and Flight Lieutenant Michael Pugh-Davis, No. 43 (Fighter) Squadron, Cornwall, England, Northern Scotland, February 24th 1988. (B) A...
Aircraft kits (injection)
Catalogue
£33.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
Airfix AX12013
1:72
Avro Vulcan B.2 BLACK BUCK Includes Shrike Missiles and ECM equipment, allowing you to faithfully recreate the Avro Vulcan B.2 that conducted in the famous Black Buck raids.
Aircraft kits (injection)
Catalogue
£72.49
-
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
Airfix AX02108A
Supermarine Spitfire Mk.Vc A02108A - Supermarine Spitfire Mk.Vc, 'Central Railways Uruguayan Staff' presentation aircraft, No.129 (Mysore) Squadron, Royal Air Force Ibsley, Hampshire, England, 31st May 1943. (A) A02108A - Supermarine Spitfire Mk.Vc, Aircraft flown by Flight Sergeant R J Dunn, No.85 Squadron, Royal Australian Air Force, Western Australia, August 1945. (B)
Aircraft kits (injection)
Catalogue
£11.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
Airfix AX01004B
North-American P-51D Mustang
Aircraft kits (injection)
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

Airfix AX02105
Re-issue Folland Gnat T.1 Scheme 1: RAF 4 FTS XS100/57 (1970) Scheme 2: Oscar EW-5894 Fallus USS Essess 1991 The diminutive Folland Gnat was originally developed as a light and affordable jet fighter, but went on to be used extensively by the Royal Air Force as an advanced fast jet training aircraft. Entering RAF service in 1959, the Gnat was responsible for training many hundreds of future fast jet pilots during its 20...
Aircraft kits (injection)
Catalogue
£10.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
Airfix AX04057A
Hawker-Siddeley Harrier GR.1/McDonnell-Douglas AV-8A Harrier
Aircraft kits (injection)
Catalogue
£23.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
Airfix AX06022A
1:72
Blackburn Buccaneer S.2B GULF WAR
Aircraft kits (injection)
Catalogue
£33.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
Airfix AX03081A
1:72
Messerschmitt Bf-110E/E-2 TROP Messerschmitt Bf-110E TROP 7/ZerstĦrergeschwader 26, Derna, Libya, 1942. (A) Messerschmitt Bf-110E, Aircraft flown by Felix Maria Brandis, 13.(Z) Jagdgeschwader 5, Norway, 1941. (B)
Aircraft kits (injection)
Catalogue
£17.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
Airfix AX03090A
Messerschmitt Me-262A-1a/2a Messerschmitt Me262A-1a, W.Nr110956 'White 17', flown by Obstlt. Heinz Bƒ¤r, III./Ergƒ¤nzungs-Jagdgeschwader 2, Lechfeld, Bavaria, Germany, 29th April 1945. (A) Messerschmitt Me262A-2a, W.Nr.500200, flown by Fj.Ofw. Hans Frƒ¶lich, 2./Kampfgeschwader 51, Fassberg, Lower Saxony, Germany, 8th May 1945. (B)
Aircraft kits (injection)
Catalogue
£17.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
Airfix AX01005B
Mitsubishi A6M2b Zero
Aircraft kits (injection)
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
Airfix AX50191
1:72
Dambusters 80th Anniversary Gift Set Avro Lancaster and Lockheed Martin F-35B Lightning II
Aircraft kits (injection)
Catalogue
£54.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
Airfix AX50190
1:72
Then & Now Supermarine Spitfire Mk.Vc & Lockheed F-35B Lightning II
Aircraft kits (injection)
Catalogue
£29.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
Airfix AX55010
Lockheed Martin F-35B Lightning II Starter Set
Aircraft kits (injection)
Catalogue
£19.99
£17.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
Airfix AX09007A
Avro Lancaster B.III (SPECIAL) 'THE DAMBUSTERS' Avro Lancaster B.III (SPECIAL) 'THE DAMBUSTERS', Aircraft flown by Wing Commander Guy Gibson, Commanding Officer, "Operation Chastise", No.617 Squadron, Royal Air Force Scampton, England, 16/17th May, 1943. (A) Avro Lancaster B.III (SPECIAL) 'THE DAMBUSTERS', Aircraft flown by Squadron Leader John Leslie (Les) Munro, "Operation Chastise" , No.617 Squadron, Royal Air Force S...
Aircraft kits (injection)
Catalogue
£46.99
-
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
Airfix AX02110
Hawker Tempest Mk.V Post War
Aircraft kits (injection)
Catalogue
£11.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
Airfix AX04065
de Havilland Mosquito PR.XVI A, No.60 Squadron South African Air force, San Savero, Province of Foggia, Italy, January 1945. (A) B, 635th Bomb Squadron, 25th Bomb Group, 325th Photographic Wing, United States Army Air Force, Royal Air Force Watton, Norfolk, England, September 1944. (B)
Aircraft kits (injection)
Catalogue
£23.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
Airfix AX02099V
Commonwealth CA-13 Boomerang
Aircraft kits (injection)
Catalogue
£10.99
-
In Stock
20+

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
Airfix AX02043V
Lockheed F-80C Shooting Star
Aircraft kits (injection)
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
Airfix AX55307B
Westland Sea King HAR.3 (gift or starter set with paints, paint brush and poly cement)
Aircraft kits (injection)
Catalogue
£24.49
-
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
Airfix AX04054A
BAC/EE Lightning F.2A
Aircraft kits (injection)
Catalogue
£23.99
-
In Stock
5

   1   |   2   |   3   |   4      »      [4]

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