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

Re-released! de Havilland Sea Venom exterior (designed to be used with Eastern Express, FROG, IOM, Novo and Toko kits) [5279-18] [S.E.203 'Aquilon' [ex-Frog Sea Venom]
Aircraft detailing sets (etched)
New Arrivals
16/02/2024
£6.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
Albatros D.III Oeffag 153/253 Austro-Hungarian with 6 Painting shemes! The Oeffag aircraft were built in three main versions (series 53.2, 153, 253) using the 138, 149, or 168 kW (185, 200, or 225 hp) Austro-Daimler engines respectively. All Oeffag variants were armed with two 8 mm (.315 in) Schwarzlose machine guns. In most aircraft, the guns were buried in the fuselage, where they were inaccessible to the pilot. In servic...
Aircraft kits (resin)
New Arrivals
16/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
Albatros D.III Oeffag 153/253 In Polish Service with 5 Painting shemes! + Sworl decals! The Oeffag aircraft were built in three main versions (series 53.2, 153, 253) using the 138, 149, or 168 kW (185, 200, or 225 hp) Austro-Daimler engines respectively. All Oeffag variants were armed with two 8 mm (.315 in) Schwarzlose machine guns. In most aircraft, the guns were buried in the fuselage, where they were inaccessible to the...
Aircraft kits (resin)
New Arrivals
16/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
Hanriot HD.1/HD.2 with 2 engines (Le Rhone & Clerget) 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 ...
Aircraft kits (resin)
New Arrivals
16/02/2024
£249.99
£224.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
Hanriot HD.2 Float 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-etch...
Aircraft kits (resin)
New Arrivals
16/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
Hanriot HD.I Kit-set contains: Resin Casting Parts: Fuselage, wings (steel wire reinforced), horizontal and vertical fins, ailerons, tail wings. 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 parts - ste...
Aircraft kits (resin)
New Arrivals
16/02/2024
£189.99
£170.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
1:32
Hanriot HD.2 Floats 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. - phot...
Aircraft kits (resin)
New Arrivals
16/02/2024
£189.99
£170.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
Hanriot HD.2 with Clerget 9B engine. On wheels
Aircraft kits (resin)
New Arrivals
16/02/2024
£189.99
£170.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
Halberstadt D.II with Mercedes D.II engine Resin Casting Parts: Fuselage, wings (steel wire reinforced only 1:32), horizontal and vertical fins, ailerons, tail wings. 3D-Printed parts: Full Interior, propeller, gun, undercarriage. All struts, strong ABS PRO 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
16/02/2024
£196.20
£176.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
Curtiss JN-4D Jenny
Aircraft kits (resin)
New Arrivals
16/02/2024
£206.99
£186.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
Porsche 959 3D-Printed & coloured Interior on decal paper (designed to be used with Tamiya kits)
3D printed decal
New Arrivals
16/02/2024
£32.40
£29.16
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 P-40E Warhawk 3D-Printed & coloured Interior on decal paper (designed to be used with Trumpeter kits)
3D printed decal
New Arrivals
16/02/2024
£21.60
£19.44
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
Grumman F6F-3 Hellcat 3D-Printed & coloured Interior on decal paper (designed to be used with Eduard kits)
3D printed decal
New Arrivals
16/02/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
1:48
Vought F4U-1A Corsair 3D-Printed & coloured Interior on decal paper (designed to be used with Magic Factory kits)
3D printed decal
New Arrivals
16/02/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
Bristol Beaufort Mk.I 3D-Printed & coloured Interior on decal paper (designed to be used with ICM kits) (with 3D-printed resin parts)
3D printed decal and resin
New Arrivals
16/02/2024
£42.20
£37.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
Macchi C.202 Folgore Early 3D-Printed & coloured Interior on decal paper (designed to be used with Eduard and Hasegawa kits)
3D printed decal
New Arrivals
16/02/2024
£24.80
£22.32
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
Macchi C.202 Folgore Late 3D-Printed & coloured Interior on decal paper (designed to be used with Eduard and Hasegawa kits)
3D printed decal
New Arrivals
16/02/2024
£24.80
£22.32
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
Macchi C.205 Veltro 3D-Printed & coloured Interior on decal paper (designed to be used with Hasegawa and Italeri kits)
3D printed decal
New Arrivals
16/02/2024
£24.80
£22.32
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
Grumman F6F-3E/N Hellcat 3D-Printed & coloured Interior on decal paper (designed to be used with Eduard kits)
3D printed decal
New Arrivals
16/02/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
Fairey Fulmar Mk.I 3D-Printed & coloured Interior on decal paper (designed to be used with Trumpeter kits)
3D printed decal
New Arrivals
16/02/2024
£24.80
£22.32
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
Westland Sea King HAS.1 3D-Printed & coloured Interior on decal paper (designed to be used with Airfix kits)
3D printed decal
New Arrivals
16/02/2024
£32.40
£29.16
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
Westland Sea King HAS.5 3D-Printed & coloured Interior on decal paper (designed to be used with Airfix kits)
3D printed decal
New Arrivals
16/02/2024
£37.80
£34.02
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
Westland Sea King HU.5 3D-Printed & coloured Interior on decal paper (designed to be used with Airfix kits)
3D printed decal
New Arrivals
16/02/2024
£26.99
£24.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
Porsche 959 3D-Printed & coloured Interior on decal paper (designed to be used with Tamiya kits) (Small version)
3D printed decal
New Arrivals
16/02/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
Bristol Beaufort Mk.I 3D-Printed & coloured Interior on decal paper (designed to be used with ICM kits) (Small version)
3D printed decal
New Arrivals
16/02/2024
£12.99
£11.69
Out of Stock
0