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 = 956 AND P.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 663 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

Scale

Product Type

Manufacturer

Added Within

Manufacturer & Catalogue No ASC DESC
Scale ASC DESC
Description ASC DESC
Type ASC DESC
Department ASC DESC
Delivered 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

RS Models RSMI92166
Yakovlev Yak-11 / C-11 "Moose" 4 decal variants for CSSR, Hungarian, Poland, Mali 4 decal variants 1. C-11, No. 171707, 47. prƒ...é¯z. let. pluk, 2. let., Mlada, 1956 2. C-11, Hungarian Air Force 3. C-11, Gdynia - Oksywie, Babie Doly, Poland, 1960 4. C-11, Mali Air-force, 1961 The Yakovlev design bureau began work on an advanced trainer based on the successful Yak-3 fighter in mid 1944, although the trainer wa...
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.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
RS Models RSMI94017
Bucker Bu-131A/D 2 decal v. for RomaniaThe Bƒ¼cker Bƒ¼ 131 Jungmann was a German two-seat biplane trainer used during World War II. The aircraft was designed by Swedish-born designer Ing. Anders J. Anderson, who led all design work at Bƒ¼cker until 1939. 1. Bƒ¼cker 131A, Aeroclub Prahova 1939 2. Bƒ¼cker 131D, Romanian AF, 1941 3. Bƒ¼cker 131D, Romanian AF, 1942 The first flight of the prototype Bƒ¼ 131 (D-3150) was...
Aircraft kits (injection)
New Arrivals
03/04/2024
£22.99
£20.69
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
RS Models RSMI94018
Avia Ba.122 1 decal v. for Czechoslovakia. In the spring of 1934, the army leadership decided on the participation of Czechoslovak pilots in the international aerobatic competition Coupe Mondial, which was to be held in June in Paris. Under the leadership of ing. Novotny and with the help of acrobats rtm. Novƒik and Sgt. Ambruz, a new aerobatic aircraft B-222 was created in six weeks. 1. Avia Ba.122.17, 4th Internationa...
Aircraft kits (injection)
New Arrivals
03/04/2024
£22.99
£20.69
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
RS Models RSMI94019
Avia Ba.322.1 1 decal v. for Czechoslovakia 1. Avia B...i.322.1, III. Nƒirodnƒ let RAl'S a II. Nƒirodnƒ soutAº...¾ v leteckƒ© akrobacii. Kbely 1937 The engine was a Castor II-A. During July 1937, B...i-122 machines were assigned to the training squadron of Air Regiment 4 and to the VLU. Modified B...i-322 and B-322 aircraft were built from two B...i-122s. The B...i-322 type was powered by an Rk-17 engine and had hooded wheels. He fl...
Aircraft kits (injection)
New Arrivals
03/04/2024
£22.99
£20.69
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
Air-Graphic Models AIR72-025
1:72
Italian and Brazilian AMX and AMX-T Ghibli collection AMX Ghibli (A-11A & A-11B) Decal Collection 1/72 Scale Featuring 12 aircraft. 8 Italian Air Force including the AMX-T 4 Brazilian Air Force including the A-11A and A-11B
Aircraft decals (military)
New Arrivals
28/03/2024
£24.60
£22.14
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
Air-Graphic Models AIR72-026
1:72
World Update Set Part 6 This features: Italian Navy McDonnell-Douglas AV-8B+ Harrier 30th Anniversary Splinter Scheme Spanish Navy AV-8B+ Harrier/Matador RIAT 2023 Special Italian Air Force HH-101A Caesar RIAT 2023 (New colour scheme) Greek Navy S-70B-6 Aegean Hawk Royal Australian Navy SH-2G(A) Seasprite 805 Squadron Singapore Air Force F-16D Peace Canvin II Special Scheme Italian Air Force Eurofighter EF2000 4 Storm...
Aircraft decals (military)
New Arrivals
28/03/2024
£31.50
£28.35
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
HAD Models HUN72271
Lockheed KC-130F Fat Albert 0690
Aircraft decals (military)
New Arrivals
28/03/2024
£16.20
£14.58
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
HAD Models HUNE721005
Grumman A-6E/KA-6D Intruder VA-35 The Black Panther aâé-The final Countdownaâé-. Aircraft stencilling with sewing patches and Helmet markings
Aircraft decals (military)
New Arrivals
28/03/2024
£16.20
£14.58
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
HAD Models HUNE721006
Saab JAS-39 Gripen Tigermeet 2023 HUNAF
Aircraft decals (military)
New Arrivals
28/03/2024
£16.20
£14.58
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
Revell RV3358
Sd.Kfz.164 Nashorn
Military vehicle kits (injection)
New Arrivals
28/03/2024
£16.99
£15.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
Revell RV3797
Mitsubishi Ki-21-la "Sally"
Aircraft kits (injection)
New Arrivals
28/03/2024
£38.99
£35.09
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

Sword SW72077
Re-released! McDonnell F2H-2 Banshee 2 markings for USN Kit contains PUR and PE parts
Aircraft kits (injection)
New Arrivals
28/03/2024
£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

Sword SW72078
Re-released! McDonnell F2H-2P photo Banshee 2 markings for USN and Marines Kit Contains PUR and PE parts
Aircraft kits (injection)
New Arrivals
28/03/2024
£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

No Image
Sword SW72150
Re-released! Vought RF-8G Crusader Photo-Recon over Vietnam 2 markings: VFP-63,146845,NP603,USS Hancock 1971 VFP-63,1456445,PP 907,USS Hancock 1967 Kit Contains masks and PUR of Ejection seat.
Aircraft kits (injection)
New Arrivals
28/03/2024
£24.99
£22.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
Eduard kits EDK2145
Messerschmitt Bf-109G GUSTAV pt. 2 DUAL COMBO The Limited edition of the kit of the famous German WWII fighter aircraft Bf-109G in 1/72 scale. The kit offers aircraft of G-6 late and G-14 versions with Erla haube canopy. - plastic parts: Eduard- marking options: 10- decals: Eduard- PE parts: yes, pre-painted- painting mask:yes- resin parts: no 1 / 10 COL A Messerschmitt Bf-109G-6/R6, WNr. 160303, Hptm. Friedrich Eberle, CO ...
Aircraft kits (injection)
New Arrivals
28/03/2024
£25.50
£22.95
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

Eduard kits EDK7077
RE-RELEASED! Grumman F6F-5 Hellcat 1/72 The ProfiPACK edition kit of US carrier based WWII fighter plane F6F-5 Hellcat in 1/72 scale.- plastic parts: Eduard- marking options: 4- decals: Eduard- PE parts: yes, pre-painted- painting mask: yes- resin parts: no 1 / 4 aâé-~Minsi III, BuNo 70143, Cdr. David McCampbell, USS Essex, October 25th, 1944 2 / 4 White 7 aâé-~Paper Doll, Lt. Carl A. Brown Jr., VF-27, USS Princeton, O...
Aircraft kits (injection)
New Arrivals
28/03/2024
£13.99
£12.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
Eduard ED3DL72029
North-American/Rockwell OV-10D+ Bronco SPACE 1/72 (designed to be used with ICM kits)
3D printed decal
New Arrivals
28/03/2024
£9.20
£8.28
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
Eduard ED3DL72030
Martin PBM-5A Mariner SPACE 1/72 (designed to be used with Academy kits)
3D printed decal
New Arrivals
28/03/2024
£15.70
£14.13
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
Eduard Brassin ED672353
Messerschmitt Bf-109G-2 /Bf-109G-4 engine 3D-Printed 1/72 (designed to be used with Eduard kits)
Aircraft engines and propellers (resin)
New Arrivals
28/03/2024
£17.20
£15.48
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
Eduard Brassin ED672354
1:72
Messerschmitt Me-410A-1/U-2 wheels 1/72 (designed to be used with Airfix kits)
Aircraft wheels (resin)
New Arrivals
28/03/2024
£5.30
£4.77
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
Eduard Brassin ED672355
Messerschmitt Bf-109G-5/Bf-109G-6 gun barrels 3D-Printed 1/72 (designed to be used with Eduard kits)
Aircraft guns (resin)
New Arrivals
28/03/2024
£2.50
£2.25
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
Eduard Brassin ED672356
1:72
Messerschmitt Me-410A-1/U-2 exhaust stacks 3D-Printed 1/72 (designed to be used with Airfix kits)
Aircraft exhausts (resin)
New Arrivals
28/03/2024
£3.80
£3.42
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
Eduard Brassin ED674004
1:72
Messerschmitt Bf-109G-2 LĦĦK 1/72 (designed to be used with Eduard kits)
Aircraft detailing sets (resin)
New Arrivals
28/03/2024
£7.99
£7.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
Eduard Brassin ED674005
Messerschmitt Bf-109G-4 LĦĦK 1/72 (designed to be used with Eduard kits)
Aircraft detailing sets (resin)
New Arrivals
28/03/2024
£7.99
£7.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
Eduard ED72735
Martin PBM-5A Mariner exterior 1/72 (designed to be used with Academy kits)
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£19.60
£17.64
Out of Stock
0

[1]   «      9   |   10   |   11   |   12   |   13      »      [27]
-10 | +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