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.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 1,954 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 113

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

No Image
Toyota Celica LB Turbo Gr.5
Car kits (injection)
New Arrivals
07/03/2024
£44.99
£40.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
Panzerkampfwagen IV Ausf.G Early Production & Motorcycle Set aâé-l"Eastern Frontaâé-
Military vehicle kits (injection)
New Arrivals
07/03/2024
£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
Italian bomb set. Includes 2x 250kg, 4x100kg, 4x 50kg bomb 3D-Printed
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£24.50
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
Italian bomb set II. (5x 250kg) includes 5x 250kg bomb
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£24.50
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
CANT Z.1007bis engine and cowling set Supermodel 3 pcs, each consisting of four parts (cowling, a pair of exhausts and engine)
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£39.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
Savoia-Marchetti SM.79 Sparviero engine and cowling set, aâé-l"harmonicaaâé- exhausts Italeri, for torpedo machines from 1941-42 3 pcs per pack, each consisting of three parts (cowling, exhaust ring and engine) (designed to be used with Italeri kits)
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£39.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
CANT Z.506 Airone propeller set Supermodel/Italeri. Suitable for CANT Z.501 as well. 3 spinners and 10 prop blades (ona extra) per pack
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£17.50
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
CANT Z.1007bis propeller set (designed to be used with Supermodel kits) 3 prop hubs and 10 prop blades (one extra) per pack
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£18.60
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
Italian bomb set. Includes 2x 250kg, 4x100kg, 4x 50kg bomb 3D-Printed
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£17.50
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
Italian bomb set II. (5x 250kg) includes 5x 250kg bomb
Aircraft detailing sets (resin)
New Arrivals
07/03/2024
£17.50
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
RAF Mustang roundels & fin flashes, 2 sets insignia diameters: Type A (40"), A1 (30,35"), B (32,40"), C (32,40"), C1 (36") scale diameters: Type A (7.0 mm), A1 (5.3;6.2 mm), B (5.6;7.0 mm), C (5.6;7.0 mm), C1 (6.4 mm) fin flashes: 24x24", 24x27" scale sizes: 4.2x4.2; 4.2x4.8 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Typhoon roundels & fin flashes, 2 sets insignia diameters: Type A (32,42"), A1 (35,42"), B (35,50"), C (32,50"), C1 (36") scale diameters: Type A (5.6;7.4 mm), A1 (6.2;7.4 mm), B (6.2;8.8 mm), C (5.6;8.8 mm), C1 (6.4 mm) fin flash: 24x24" scale size: 4.2x4.2 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
1:144
Tempest roundels & fin flashes, 2 sets insignia diameters: Type B (50"), C (32,36,50"), C1 (36,50"), D (36,50") scale diameters: Type B (8.8 mm), C (5.6;6.4;8.8 mm), C1 (6.4;8.8 mm), D (6.4;8.8 mm) fin flashes: 24x24" scale size: 4.2x4.2 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mohawk, Tomahawk & Kittyhawk roundels & fin flashes, 2 sets insignia diameters: Type A (38,45"), A1 (35,45"), B (48"), C (32,35"), C1 (36") scale diameters: Type A (6.7;7.9 mm), A1 (6.2;7.9 mm), B (8.5 mm), C (5.6;6.2 mm), C1 (6.4 mm) fin flashes: 24x24", 24x27" scale sizes: 4.2x4.2; 4.2x4.8 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
1:144
Hurricane roundels & fin flashes (Pt.2), 2 sets Hurricane roundels & fin flashes (Pt. 2), 2 sets insignia diameters: Type B (32,36,49"), B1 (49"), C (32,49"), C1 (18,36"), SEAC (16") scale diameters: Type B (5.6;6.4;8.5 mm), B1 (8.5 mm), C (5.6;8.5 mm), C1 (3.2;6.4 mm), SEAC (2.8 mm) fin flashes: 18x18", 24x24", SEAC 16x24" scale sizes: 3.2x3.2; 4.2x4.2; 2.8x4.2 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Fw-190 crosses & swastikas, 2 sets Balkenkreuz sizes: 600,800,900 mm scale sizes: 4.2;5.6;6.3 mm Swastikas: size 420,480,530 mm scale sizes: 2.9;3.3;3.7 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Unarmed Hawker Hurricanes Decals for the following aircraft are included: Hurricane Mk.I, G-AFKX (ex-L1606), Hawker Aircraft demonstrator & trials a/c Hurricane Mk.IIC(Met), PZ818, 5O-K, No.521 Sq., Royal Air Force Hurricane Mk.IIC, LF363, Fighter Command Communications Sq., Royal Air Force Hurricane Mk.IIC/Trop, 2-13, Training School, Imperial Iranian Air Force These decals were designed to match any 1/144 Hawker Hurric...
Aircraft decals (military)
New Arrivals
07/03/2024
£5.40
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
RAF Airacobra roundels & fin flashes, 2 sets insignia diameters: Type A (25.5,30"), A1 (34"), B (48"), C (32"), C1 (36") scale diameters: Type A (4.5;5.3 mm), A1 (6.0 mm), B (8.5 mm), C (5.6 mm), C1 (6.4 mm) fin flashes: 24x22", 24x27", slanted fin flash: 24x27" scale sizes: 4.2x3.9; 4.2x4.8 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Unarmed Hawker Hurricanes Mk.I/Mk.IIC Unarmed Hurricanes Decals for the following aircraft are included: Hurricane Mk.I, G-AFKX (ex-L1606), Hawker Aircraft demonstrator & trials a/c Hurricane Mk.IIC(Met), PZ818, 5O-K, No.521 Sq., Royal Air Force Hurricane Mk.IIC, LF363, Fighter Command Communications Sq., Royal Air Force Hurricane Mk.IIC/Trop, 2-13, Training School, Imperial Iranian Air Force These decals were designed to...
Aircraft decals (military)
New Arrivals
07/03/2024
£11.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
Irish Air Corps insignia 1939-54, 2 sets
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Irish Air Corps insignia 1954-present, 2 sets
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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

GMC 2 1/2 6x6 Truck NEW DECALS SHEET FOR 3 VERSIONS - COLOR INSTRUCTION SHEET FIGURE NOT INCLUDED The GMC 2 é½ ton truck was built in an exceptionally high number of units and can be considered the backbone of the logistical operations of the U.S. Army during World War II. Characterized by a very robust and reliable structure, its strength lay in its excellent off-road performance, thanks to its six-wheel drive and effec...
Military vehicle kits (injection)
New Arrivals
07/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
de Havilland Mosquito FB.VI 'Amiens Prison Raid' Description Colour schemes included in the kit: 1) de Havilland DH.98 Mosquito FB Mk.VI (Srs.II), HX922, Sky EG-F, No.487 Sq., Operation aâé-~Ramrod 564?, flown by Gp Capt P.C. Pickard & Flt Lt J. Broadley, Royal New Zealand Air Force, Hunsdon airfield, Hertfordshire, 18 February 1944 2) de Havilland DH.98 Mosquito FB Mk.VI (Srs.II), LR333, Sky EG-R, No.487 Sq., Operatio...
Aircraft kits (injection)
New Arrivals
07/03/2024
£23.70
£21.33
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
Re-released! Mil Mi-2 Hoplite 'Warsaw Pact' (2in1 = 2 kits in 1 box) (Soviet AF, Czechoslovak AF, Polish AF, East German AF) Designed by the Soviet Mil Design Bureau in late 1950s and exclusively produced in Poland by the WSK "PZL-ƒ......iwidnik" factory, the Mi-2 was a small transport and utility helicopter. The Mil' s prototype first flew in 1961, while the first ƒ......iwidnik-built example took to the air in November 1965. A n...
Aircraft kits (injection)
New Arrivals
07/03/2024
£17.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
Hawker Tempest Mk.V 'Fighter Aces' Description The Hawker Tempest Mk.V was the RAFs last piston-engined fighter used in the WWII, attaining great success in destruction of the German V-1 flying bombs. Developed in late 1941 from the troublesome Typhoon interceptor, the prototype flew in September 1942. The first order was for 400 Tempests, produced as the Mk.V Series 1 (100 a/c by Hawker) and Series 2 (300 a/c by Gloste...
Aircraft kits (injection)
New Arrivals
07/03/2024
£17.99
£16.19
Out of Stock
0