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

No Image
AIM-9L Sidewinder Missile Set - Contains two detailed missiles in injection resin with etched canards and fins with all the rolleron detail included. They are supplied with nose safety caps and RBF tags These will fit to the the rails on your BAe Harrier GR3 or Sea Harrier kits, which are supplied with interface detailing for the kit missile launch rails. (designed to be used with Airfix kits)
Aircraft detailing sets (mixed-media)
New Arrivals
27/02/2024
£21.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
Royal Navy BAe Sea Harrier FRS.1 Display Set - This set will help you make an interesting display scene for your big Airfix Sea Harrier. It provides a super-detailed Access Ladder Kit, Two detailed FAA Wheel Chocks, a pair of detailed FOD Guards for the kit intakes, aâé-~Remove Before Flight Tags and Pins and 'Aircraft Armed Signs and backing plates. (designed to be used with Airfix kits)
Aircraft detailing sets (mixed-media)
New Arrivals
27/02/2024
£25.30
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
Panavia Tornado GR.1/1A/4/4A Detail and Conversion Set - (designed to be used with Revell kits) - This large new-tool set in photo-etched nickel silver, resin and cast metal includes all that you need for a fully-detailed RAF machine.With full cockpit detail and ejector seat assemblies and details and printed instrument panel films. All canopy framing, mirrors and sill detailing. Complete new front undercarriage and replacem...
Aircraft detailing sets (resin)
New Arrivals
27/02/2024
£77.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
Panavia Tornado GR4/4A Twin Shoulder Rail Inner Wing Pylons - (designed to be used with Italeri and Revell kits) - This set provides a pair of the dual shoulder rail inner wing pylons that were fitted to many Tornadoes in their final years of service. These resin rails have all the fine surface detail of the real thing and come with rebatesmatched to the Flightpath AIM-9L Missile Sewrt and cast stub pylons that can be used wit...
Aircraft detailing sets (resin)
New Arrivals
27/02/2024
£9.30
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
Bae Harrier GR.1/GR.3 Access Ladder - Etched ladder supplied with all brass wire/rod to complete (designed to be used with Kinetic Model kits)
Aircraft detailing sets (resin)
New Arrivals
27/02/2024
£7.80
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
Curtiss Jenny JN-4D Premium (decals and turnbuckles)
Aircraft kits (resin)
New Arrivals
26/02/2024
£268.20
£241.38
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
Curtiss Jenny JN-4 Premium (decals and turnbuckles)
Aircraft kits (resin)
New Arrivals
26/02/2024
£268.20
£241.38
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
Halberstadt D.II Kit-set contains: Resin Casting Parts: Fuselage, wings (steel wire reinforced), horizontal and vertical fins, ailerons, tail wings, floats (HD2) 3D-Printed parts: Full Interior, propeller, guns (2 pcs.), undercarriage. All struts, strong ABS resin printed with holes to easier connect parts with wire. 3D-Printed parts packed in an additional box to ensure maximum protection. - photo-etched...
Aircraft kits (resin)
New Arrivals
26/02/2024
£134.99
£121.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
1:48
Curtiss JN-4 Jenny
Aircraft kits (resin)
New Arrivals
26/02/2024
£142.20
£127.98
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
Curtiss JN-4D Jenny
Aircraft kits (resin)
New Arrivals
26/02/2024
£142.20
£127.98
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
USN / USMC Fairchlid R4Q / C-119 "Packet" / "Flying Boxcar" Available February 24 By Nicholas M. Williams 72-pages, 140 b&w photos, 5 color photos, 22-patches, and 19 drawings. In 1949 the US Navy was looking for a tactical assault transport for its Marine Corps units and arrangements were made to divert 8 off the shelf C-119Bs from the USAF to the USMC. These 8 aircraft and the 31 C-119Cs that followed were designat...
Aircraft books
New Arrivals
26/02/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
Nakajima -Fokker Super Universal First Japanese Airliner
Aircraft kits (injection)
New Arrivals
23/02/2024
£36.99
£33.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
1:72
Manshu-Fokker Super Universal Manchukuo MKKK service
Aircraft kits (injection)
New Arrivals
23/02/2024
£36.99
£33.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

Re-released! Trolley - Accumulator (Late Version) 3D-Printed For use with AX14003A North-American P-51K Mustang AX19002 Hawker Typhoon Mk.IB AX25001A de Havilland Mosquito FB Mk.VI (designed to be used with Airfix kits)
Diorama accessories
New Arrivals
23/02/2024
£27.90
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
Hawker Hunter F.6 & BAe Harrier GR.3 Crew Ladder A crew ladder designed to be used with the Airfix kit of the Harrier. 3D-Printed
Diorama accessories
New Arrivals
23/02/2024
£21.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
RE-RELEASED! Hawker Hunter F.6 & BAe Harrier GR.3 crew ladder. 3D-Printed (designed to be used with the Revell kits) of the Hawker Hunter FGA.9/Mk.58. Hawker Hunter F.6 & BAe Harrier GR.3 Crew Ladder A crew ladder designed to be used with the Revell and Echelon Vacuform kits of the Hawker Hunter, and the Revell and Trumpeter kits of the Harrier.
Diorama accessories (resin)
New Arrivals
23/02/2024
£16.20
-
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

RE-RELEASED! BAC TSR-2 Crew Access Ladder Set (set of 2 ladders). (designed to be used with Airfix kits) 3D-Printed
Diorama accessories
New Arrivals
23/02/2024
£9.00
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! Royal Navy F-59N deck tractor . This is a highly detailed 3D-Printed resin item (it is designed be used with models of any current Royal Navy small or medium sized aircraft, and the appropriate AIM ground equipment tow bar).
Diorama accessories (resin)
New Arrivals
23/02/2024
£25.20
-
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
Re-released! Royal Navy F-59N deck tractor & fire engine . This is a highly detailed 3D-Printed resin item (it is designed be used with models of any current Royal Navy small or medium sized aircraft, and the appropriate AIM ground equipment tow bar)
Diorama accessories (resin)
New Arrivals
23/02/2024
£25.20
-
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
Crowd Control Barriers 3D-printed A set of ten barriers, which can be linked together for air show dioramas. Each section is 45mm long
Diorama accessories (resin)
New Arrivals
23/02/2024
£18.00
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
USAF fighter revetments (each section is 750mm long) 3D-Printed
Diorama accessories (resin)
New Arrivals
23/02/2024
£57.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

Re-released! Airfield Windsock 3D-Printed
Diorama accessories (resin)
New Arrivals
23/02/2024
£9.00
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

1:72
Re-released BAC TSR-2 Crew Access Ladder Set (set of 2 ladders). (designed to be used with Airfix kits) 3D-Printed
Diorama accessories (resin)
New Arrivals
23/02/2024
£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
Crowd Control Barriers (back of 10 each one is 30mm long) 3D-Printed
Diorama accessories (resin)
New Arrivals
23/02/2024
£12.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
Re-released! USAF R-11 Generation 1 aircraft Refueller 3D-Printed
Diorama accessories (resin)
New Arrivals
23/02/2024
£43.20
-
In Stock
1