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 = 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 54 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 AC12361
Beechcraft T-34B Mentor "VT-5 Training Air Wing" Training aircraft used by U.S. Navy. Reboxed Minicraft with new decals. Detailed cockpit & landing gear. Choice of 3 markings: (1) T-34B BuNo 140950, USN Training Sqn 5 (VT-5), 1976; (2) T-34B BuNo 144090, USN Training Sqn 5 (VT-5), 1984; (3) T-34B BuNo 140702, USN Training Sqn 1 (VT-1), Air Station Saufley Field FL, 1976. Masking for canopy included.
Aircraft kits (injection)
Catalogue
£17.99
£16.19
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 AC12703
1:48
Re-isssue!! Sikorsky MH-53E SEA Dragon 'U.S.Navy Mine Hunter and Combat Transport'
Aircraft kits (injection)
Catalogue
£64.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 AC12348
Fairchild A-10C Thunderbolt II "75th FS Flying Tigers". NEW TOOLNG IN 2023 Low-flying subsonic ground-attack twin-fanjet aircraft, maiden flight in May 1972. Nicknamed "Warthog" by USAF aircrew, more than 700 A-10s were built from 1973 to 1982 and the type is still in service with the US Air Force. A-10C is the current platform standard with further potential for life-extension; plans to retire the A-10 and replace with the ...
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

Academy AC12227
1:48
Mikoyan MiG-29AS Slovakian Air Force (Limited Edition)
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

No Image
Academy AC12350
Vought SB2U Vindicator "Battle of Midway" Early WWII US Navy carrier-based dive bomber. Limited edition Battle of Midway boxing. Paint mask for clear parts & photo-etch included. Choice of 2 markings
Aircraft kits (injection)
Catalogue
£27.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 AC12352
Mitsubishi A6M2b Zero Fighter Model 21 "Battle of Midway" Imperial Japanese Navy Air Service shipboard fighter aircraft, A6M Type 0, Reisen or Zero (Allied reporting name Zeke). Japan's best fighter of WWII until the Battle of Midway, June 1942. The Japanese fleet suffered heavy losses inflicted by the US, in retribution for the attack on Pearl Harbor in December 1941.
Aircraft kits (injection)
Catalogue
£24.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 AC12353
1:48
Vought F4U-4 Corsair "Battle of Jangjin Reservoir" American piston-engined carrier-based fighter-bomber with characteristic bent wing design. Built in large numbers for the US Navy and Marines, seeing service in WWII and, the subject of this Academy kit, the Korean War. Markings provided in the kit recall two US Navy Corsairs which crash-landed behind enemy lines. The Jangjin Reservoir is in a mountainous region in North Ko...
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
Academy AC12345
Douglas SBD-3 Dauntless "Battle of Midway" WWII US Navy dive bomber. Battle of Midway 80th Anniversary 1942-2022.Assemble with dive flaps open or closed. Photo-etch & mask for clear parts included. Markings for 4 aircraft, June 1942.
Aircraft kits (injection)
Catalogue
£27.99
£25.19
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 AC12346
French AF Rafale C "EC 1/7 Provence 2012" French-built twin-engine multi-role fighter aircraft. Comprehensive decal sheet. Markings for 3 single-seat aircraft: (1) French Air Force Rafale C 113-HH, SPA 162 Tiger Squadron 2012; (2) Qatar Emiri Air Force (QEAF) Rafale EQ QA217; (3) Egyptian Air Force Rafale DM
Aircraft kits (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
Academy AC12347
Lockheed PV-1 Ventura USN "Solomon Islands Theatre" Medium twin-engine bomber/patrol aircraft. Assemble with bomb bay doors open or closed. Markings for 3 aircraft: (1) PV-1 "Blonde Blitz", unit unknown, Russell Islands, Solomon Sea, mid 1943; (2) PV-1 "Scooters Dream", unit unknown, Russell Islands, Solomon Sea, mid 1943; (3) PV-1 "641", VB-148, succesful raid against Japanse vessels over Truk (Chuuk) Lagoon, July 1944.
Aircraft kits (injection)
Catalogue
£42.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
Academy AC13007B
British Challenger. Main Battle Tank
Military vehicle kits (injection)
Catalogue
£16.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 AC12336
North-American B-25B Mitchell USAAF "Doolittle Raid" Battle of Midway 80th Anniversary 1942-2022. Twin-engine medium bomber raid on Tokyo led by Col. James H. Doolittle from the carrier USS Hornet on April 18th 1942. Serials for all 16 aircraft plus nose markings for No 3/02270 "Whiskey Pete", No 7/02261 "Ruptured Duck", No 9/02303 "Whirling Dervish", No 15/02267 "TNT". Includes canopy masks.
Aircraft kits (injection)
Catalogue
£35.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 AC12339
North-American B-25C/D "European Theatre" RAF twin-engined bomber supplied under the WWII Lend-Lease scheme. Markings for 4 aircraft: (1) B-25C 41-12806, Mitchell I FL218 "Nulli Secundus", 180 Sqn, 2nd Tactical Air Force, RAF Foulsham, July 1943. (2) B-25D Mitchell II FW172, 180 Sqn, 2nd Tactical Air Force, RAF Dunsfold, August 1944. (3) B-25D Mitchell II FW239, RAF 226 Sqn, May 1944. (4) B-25C Mitchell I FL684, 180 Sqn,...
Aircraft kits (injection)
Catalogue
£35.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 AC12343
Yakovlev Yak-1 "Battle of Stalingrad" Early WWII Russian Air Force fighter Markings for 6 aircraft: (1) Lt Mikhail Dmitrievich Baranov, 183rd IAP, Stalingrad Front, Autumn 1942. (2) Lt Lydia Litvjak, history's first female fighter ace, 296th AIP, Stalingrad, Spring 1943. (3) Capt B. N. Eremin, 296th AIP, Brigadirovka airfield, Ukraine, Winter 1941-42. (4) Maj Fedor Ivanovich Shinkarenko, 42nd IAP, Winter 1942. (5) 1st L...
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 AC12326
1:48
Grumman F3F-2 US Navy Fighter
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 AC12340
Grumman TBF-1C Avenger "Battle of Leyte Gulf" Multiple decal options: (1) TBF-1Cs of VC-42 USS Bogue 1944; (2) TBF-1Cs of VT-20 USS Enterprise 1944; (3) TBF/TBM-1Cs of VT-51 USS San Jacinto 1944 including LtJG George H. W. Bush's aircraft No 2; (4) TBF-1Cs of VT-2 USS Hornet 1944; (5) TBF-1C of VT-18 USS Intrepid 1944; (6) TBF-1C of VT-19 USS Lexington 1944
Aircraft kits (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
Academy AC12341
Curtiss P-40N Warhawk "Battle of Imphal" Markings for 3 aircraft: (1) P-40N-5 42-105128 "Lulu Belle" of 2nd Lt Philip Adair, 89th Fighter SQ, 80th FG, Assam India 1944; (2) P-40N-5 42-105826 of Maj Gen Gerald Johnson, 49th FG HQ, Biak (Hollandia/Jayapura) Oct 1944; (3) Kittyhawk IV "Cleopatra III" of Wg Cdr Geoff C Atherton, 80 SQ RAAF, New Guinea 1944.
Aircraft kits (injection)
Catalogue
£23.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 AC12294
McDonnell F-4C Phantom Vietnam War Moulded in multi-coloured plastic
Aircraft kits (injection)
Catalogue
£54.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 AC12338
North-American P-51/Mustang Mk.1a North Africa
Aircraft kits (injection)
Catalogue
£24.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
Academy AC12335
Douglas SBD-2 Dauntless Battle of Midway (ex Accurate Miniatures, Eduard and Italeri)
Aircraft kits (injection)
Catalogue
£27.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 AC12284
1:48
Lockheed T-33A Shooting Star Limited Edition Import
Aircraft kits (injection)
Catalogue
£25.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 AC12331
Douglas SBD-1 Dauntless Pearl Harbor WWII US Navy dive bomber Ex-Accurate Miniatures tooling. Detailed cockpit and landing gear. Assemble with dive flap closed or open. Markings for (1) SBD-1 2-MB-2 of VMSB-232 Red Devils, MCAS Ewa Mooring Mast Field, Territory of Hawaii, 7 Dec 1941; (2) SBD-1 132-B-4 of VMSB-132 Crying Red Asses, MCAS Quantico, Virginia USA late 1941. Mask tape for clear parts & photo-etch parts inclu...
Aircraft kits (injection)
Catalogue
£27.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 AC12311
1:48
Mikoyan MiG-21MF "Soviet Air Forces & Export"
Aircraft kits (injection)
Catalogue
£29.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 AC12330
Vought V-156-B1 'Chesapeake' Royal Navy (SB2U-3 Vindicator)
Aircraft kits (injection)
Catalogue
£27.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 AC12250
1:48
Hughes 500D TOW Helicopter
Aircraft kits (injection)
Catalogue
£8.99
Out of Stock
0

   1   |   2   |   3      »      [3]

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