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 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 243 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 CATAX24
Airfix Catalogue 2024 A78204
Catalogues and lists
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
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 AX09182A
Gloster Meteor F.8 A09182A - Gloster Meteor F.8, Evergreen Display Team, College of Air Warfare, RAF, 1963-64. (A) A09182A - Gloster Meteor F.8, The Meteorites Aerobatic Team, RAAF, Williamstown, 1956. (B)
Aircraft kits (injection)
Catalogue
£46.99
£42.29
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 AX12014
Blackburn Buccaneer S.2B Blackburn Buccaneer S.2B, XW527/527, No. 12 Squadron, RAF Lossiemouth, Scotland, 1993. (A) Blackburn Buccaneer S.2B, XW544, No. 15 Squadron, RAF Laarbruch Germany, 1971. (B) Blackburn Buccaneer S.2B, XV352, No. 208 Squadron, Op Red Flag, 1977. (C) Blackburn Buccaneer S.2C, XW547/R, Guinness Girl/Pauline, Operation Granby/Desert Storm, Muharraq Airport, Bahrain, 1991. The only Buccaneer in ...
Aircraft kits (injection)
Catalogue
£72.49
£65.24
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 AX02309V
Bristol Bloodhound
Military vehicle 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

Airfix AX05135
Supermarine Spitfire FR Mk.XIV
Aircraft kits (injection)
Catalogue
£28.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 AX02413V
BACK IN STOCK!!! Bond Bug For a range of vehicles which were only ever produced in relatively small numbers over a four year period, could there have possibly been a more distinctive and eye-catching vehicle found on Britain's roads than the Bond Bug? The idea behind the Bond Bug was to take the rather staid reputation of the three-wheeled vehicles produced in Britain after the war and give them a fun and sporty makeover, with...
Car kits (injection)
Catalogue
£10.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 AX18002V
Junkers Ju-87B-2 'Stuka' A18002V - Junkers Ju87B-2 Stuka, 3/St.G2 based at St. Malo/Lannion, France, August 1940. (A) A18002V - Junkers Ju87B-2/Trop Stuka, of 3/St. G1 based at Derna, Libya, 1941. (B)
Aircraft kits (injection)
Catalogue
£96.99
£87.29
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 AXJ6050
Routemaster Bus QUICK BUILD No Glue! - No paint! - Just BUILD!
Car kits (injection)
Catalogue
£29.99
£26.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 AX11007
Fairey Gannet AS.1/AS.4 Fairey Gannet AS.4, 849 Naval Air Squadron HQ training flight, Royal Naval Air Sation Culdrose, circa 1959. (A) Fairey Gannet AS.4, 815 Naval Air Squadron, HMS Ark Royal, 1958. (B) Fairey Gannet AS.1, 847 Naval Air Squadron, RAF Nicosia, Cyprus, 1957. (C)
Aircraft kits (injection)
Catalogue
£54.99
£49.49
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 AX08252V
Great Western
Ship kits, full hull (injection)
Catalogue
£34.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
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 AX50110A
Aston Martin DBR9 Set includes Poly Cement, 2 Paint Brushes and 6 Acrylic Paints
Car kits (injection)
Catalogue
£26.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 AX1379
Ferret Scout Car Mk.2 After a successful series of light armoured reconnaissance and liaison vehicles, the Ferret Scout Car saw widespread British and Commonwealth military service from the 1950s onwards, with later variants of the Ferret taking part in the Gulf War of 1990. A vehicle type which can trace its lineage back to the successful Daimler Dingo scout car of the Second World War, the Ferret was designed with stealt...
Military vehicle 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 AX55012
Land Rover Series 1 Pick Up Starter Set
Car kits (injection)
Catalogue
£14.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 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 AX11006
1:48
Westland Sea King HAS.1/HAS.5/HU.5 A11006 - Westland Sea King HAS.1 XV666, No.826 Naval Air Squadron, Royal Naval Air Station Culdrose, Cornwall, England, 1970. (A) A11006 - Westland Sea King HAS.5 XV666, No.814 Naval Air Squadron, Royal Naval Station Culdrose, Cornwall, England, 1988. (B) A11006 - Westland Sea King HU.5 XV666, No.771 Naval Air Squadron, Royal Naval Station Culdrose, Cornwall, England, 1990. (C) A1100...
Aircraft kits (injection)
Catalogue
£54.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 AX55007
Lamborghini Huracan Evo Starter Set
Car kits (injection)
Catalogue
£14.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

No Image
Airfix AX06102A
Supermarine Seafire F.XVIIC A06102A - Supermarine Seafire F.XVII, No.767 Naval Air Squadron, Royal Naval Air Station Yeovilton, Somerset, England, 1950. (A) A06102A - Supermarine Seafire F.XVII, No.807 Naval Air Squadron, HMS Vengeance, 1947. (B) A06102A - Supermarine Seafire F.XVII, 1831 Royal Naval Air Station Stretton (HMS Blackap), Chesire, England, 1947-1951. (C)
Aircraft kits (injection)
Catalogue
£33.99
-
In Stock
10+

   1   |   2   |   3   |   4   |   5      »      [10]

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