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=7874 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
Academy AC13426
Challenger 1 Mk 3 British main battle tank (MBT) in service from 1983 to 2001. Armed with a 120mm L11A5 rifled gun (retro-fitted with L30A1), 7.62mm L8A2 & 7.62mm L37A2 machine guns mounted on the turret roof. Equipment included virtually impregnable Chobham composite armour for protection against threats including kinetic energy penetrators and high-explosive anti-tank (HEAT) rounds. Other defensive measures included laser w...
Military vehicle kits (injection)
Catalogue
£11.99
£10.79
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
Academy AC12582
McDonnell F-15C ANG "75th Anniversary Medal of Honor" 2019 Special 75th anniversary markings in remembrance of 2nd Lt David R. Kingsley of the United States Army Air Forces, killed in action on 23rd June 1944 when his B-17 Flying Fortress "Sandman" was brought down during a bombing mission over the oil refineries of Ploesti, Romania. 2nd Lt Kingsley was awarded the Congressional Medal of Honor for his gallantry. Kingsley Fiel...
Aircraft kits (injection)
Catalogue
£19.99
-
In Stock
9

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
Academy AC13422
Pz.Kpfw.VI Tiger I Early Version, "Red 131", c.1943 8.8cm KwK 36 main gun & MG34 machine gun. Link & Length tracks. Choice of markings include Tiger I "131", 504th Heavy Tank Battalion, Tunisia; Tiger I "334", 503rd Heavy Tank Battalion, Russia 1943; Tiger I "411", 1st SS Panzer Division, LSSAH Kharkov, Russia 1943.
Military vehicle kits (injection)
Catalogue
£11.99
-
In Stock
11

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
Academy AC13423
German King Tiger "Henschel Turret", 1944/45 WWII German heavy tank. 8.8cm KwK 36 main gun & MG34 machine gun. Link & Length tracks. Choice of 3 markings.
Military vehicle kits (injection)
Catalogue
£11.99
-
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
Academy AC13425
1:72
USMC M60A1 RISE (P), 1990s Upgraded American medium tank introduced in 1978 with Reliability Improved Selected Equipment (Passive) including Explosive Reactive Armor (ERA) and Kevlar spall liners for the turret. 105mm M68E1 main gun & M2 heavy machine gun. Choice of 3 markings.
Military vehicle kits (injection)
Catalogue
£11.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
Academy AC12583
German WWII Horten Ho 229 "Wunderwaffe" One of Nazi Germany's "wonder weapons", the jet-powered flying wing demonstrated early characteristics of stealth technology to achieve a smaller radar cross-section than conventional attack aircraft. The project progressed no further than three prototypes built for testing
Aircraft kits (injection)
Catalogue
£18.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
Academy AC13421
Soviet T-34/85 WWII Medium Tank WWII Soviet tank equipped with 85mm ZiS-S-32 main gun & Degtyaryov (DT) machine gun. Choice of 3 markings.
Military vehicle kits (injection)
Catalogue
£11.99
£10.79
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
Academy AC12577
Boeing F/A-18F "VFA-154 Black Knights" US Navy 2-seat Super Hornet, carrier-capable, multirole fighter aircraft. Choice of colours & markings for 2 aircraft: (1) F/A-18F BuNo 166873, VFA-154 "Black Knights" CAG 100. USS Nimitz (CVN068), 2013; (2) F/A-18F BuNo 166628, VFA-11 "Red Rippers" CAG 100, USS Enterprise (CVN-65), 2012. Cartograf decals. Canopy mask included.
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
Academy AC12578
1:72
Grumman F-14B Tomcat "VF-103 Jolly Rogers Last Cruise" US Navy 2-seat Tomcat originally designated F-14+ fitted with the General Electric F110 jet engine in place of the original Pratt & Witney plant.
Aircraft kits (injection)
Catalogue
£34.99
£31.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
Academy AC12575
Sikorsky CH-53D Sea Stallion "Operation Frequent Wind" Helicopter airlift of thousands of US citizens and South Vietnamese from Saigon in South Vietnam on April 29/30 1975, before the city's takeover by the North Vietnamese People's Army of Vietnam (PAVN) and the Viet Cong, which brought the Vietnam War to an end.Assemble with tail boom straight or folded & rear ramp open or closed. Two pilot figures incuded. Markings for 2 S...
Aircraft kits (injection)
Catalogue
£21.99
£19.79
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
Academy AC12576
Lockheed F-104C Starfighter "Vietnam War" Lockheed's supersonic tactical fighter in US Air Force service from 1958. The C-type's last US active duty was from late 1965 to mid 1967 during the Vietnam War. Choice of markings & serials for USAF, ROCAF (Taiwan F-104G) & JASDF (Japan F-104J).
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
Academy AC12573
Consolidated PBY-5A Catalina "Battle of Midway" USN Battle of Midway 80th Anniversary 1942-2022. Markings for 2 aircraft: (1) BuNo 04982, VP-44, Midway June 3 1942. Ensign Jewell "Jack" Harmon Reid and crew were first to detect the Japanese fleet 700 nautical miles west of Midway; (2) BuNo unknown, VP-24, Midway June 1942. Ensign Gaylord Propst and crew attacked the IJN with torpedoes. Canopy masks included.
Aircraft kits (injection)
Catalogue
£23.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
Academy AC12448
Lockheed SR-71 Blackbird Limied Edition of 2000 pieces worldwide Markings by Cartograf for 12 aircraft: (1) 61-7974, Det 1, 9th SRW, Kadena AB, Okinawa, Japan. (2) 61-7967, same unit, late '70s. (3) 61-7978, same unit, 'Rapid Rabbit'. (4) 61-7955, Air Force Logistics Command, Palmdale. (5) 61-7976, Det 4, 9th SRW, RAF Mildenhall 1977 (6) 61-7967, Det 1, 9th SRW, Osan AB, Korea. (7) Tail nos for 961-964. (8) 61-7962...
Aircraft kits (injection)
Catalogue
£25.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

Academy AC12528
Boeing B-29A Enola Gay & Bockscar. Little Boy and Fat Man bombs included.
Aircraft kits (injection)
Catalogue
£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
Academy AC12568
McDonnell-Douglas F-15E Strike Eagle. 48th Fighter Wing. Markings for USAF 97-0219 492nd SQ & 91-0603 494th SQ, 48th Fighter Wing, RAF Lakenheath June 2019.
Aircraft kits (injection)
Catalogue
£31.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

Academy AC12450
Junkers Ju-87G-1 'Stuka' Tankbuster
Aircraft kits (injection)
Catalogue
£9.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

Academy AC12567
Boeing F/A-18F USN VFA-2 Bounty Hunters. Decal variation. New markings for s/n 165916 & 165920, VFA-2 'Bounty Hunters', USS Abraham Lincoln CVN-72 2012
Aircraft kits (injection)
Catalogue
£31.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

Academy AC12569
Lockheed-Martin F-35B Lightning II VMFA-121 Green Knights USMC Variant of the F-35, F-35B is the short take-off and vertical landing (STOVL) version. aâé-¢ 100% new tooling. aâé-¢ Detailed cockpit interior, weapons bay and landing gear. aâé-¢ Can be built in vertical landing mode, or in flight mode. aâé-¢ Landing gear, canopy and weapons bay can be built open or closed aâé-¢ Various air-to-air and air-to-ground weapons incl...
Aircraft kits (injection)
Catalogue
£31.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

Academy AC13408
WWII U.S. M3 Half Track, 1/4 Ton Amphibian ehicle & Motorbike
Military vehicle kits (injection)
Catalogue
£12.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

Academy AC13411
M1126 Stryker
Military vehicle kits (injection)
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

Academy AC12563
Grumman F-14A Tomcat VF-143 Pukin' Dogs New Tooling in 2019
Aircraft kits (injection)
Catalogue
£31.99
-
In Stock
8

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

Academy AC12422
1:72
McDonnell-Douglas F/A-18D Hornet 'US Marines' (twin-seat)
Aircraft kits (injection)
Catalogue
£21.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

Academy AC12561
1:72
Lockheed-Martin F-35A Lightning II Export. Markings for ROK, USA, Netherlands, Norway, Israel, Australia & Italy air forces.
Aircraft kits (injection)
Catalogue
£29.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

Academy AC12565
Boeing F/A-18E Super Hornet Chippy Ho
Aircraft kits (injection)
Catalogue
£28.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

Academy AC12560
1:72
Boeing E/A-18G Growler VAQ-141 Shadowhawks U.S. Navy carrier-based electronic warfare aircraft EA-18G Growler . New added parts for AN/ALQ-218 & AN-ALQ-99 electronic warfare systems, AIM-120 air-to-air missile & drop tanks included. Cartograf markings for USN reg 166928 in 2010 and 2013 and RAAF reg 169153 in 2015.
Aircraft kits (injection)
Catalogue
£29.99
-
In Stock
1

   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