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 pt.id=1880 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 70 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
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
Kriegsmarine - On duty - Part I " Precision-crafted 3D printed resin figures tailored for warship modellers. Contains 36 figures, each figure has a distinct pose or/and uniform type. Specifically designed for Kriegsmarine WWII warships." hip crew, figures, battleship crew, ION figures, naval figures, Kriegsmarine
Figures (resin)
New Arrivals
16/04/2024
£27.00
£24.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
Kriegsmarine - On duty - Part II " Precision-crafted 3D printed resin figures tailored for warship modellers. Contains 36 figures, each figure has a distinct pose or/and uniform type. Specifically designed for Kriegsmarine WWII warships." Ship crew, scale figures, battleship crew, ION figures, naval figures, Kriegsmarin
Figures (resin)
New Arrivals
16/04/2024
£27.00
£24.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
Kriegsmarine - On duty 1/200 " Precision-crafted 3D printed resin figures tailored for warship modellers. Contains 72 figures, each figure has a distinct pose or/and uniform type. Specifically designed for Kriegsmarine WWII warships." Ship crew, scale figures, battleship crew, ION figures, naval figures, Kriegsmarine
Figures (resin)
New Arrivals
16/04/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
ION MODEL Kriegsmarine - On duty 1/350 " Precision-crafted 3D printed resin figures tailored for warship modellers. Contains 72 figures, each figure has a distinct pose or/and uniform type. Specifically designed for Kriegsmarine WWII warships." Ship crew, scale figures, battleship crew, ION figures, naval figures, Kriegsmarine
Figures (resin)
New Arrivals
16/04/2024
£16.80
£15.12
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
Kriegsmarine - Chilling on deck 1/700 " Precision-crafted 3D printed resin figures tailored for warship modellers. Contains 90 figures, each figure has a distinct pose or/and uniform type. Specifically designed for Kriegsmarine WWII warships." Ship crew, scale figures, battleship crew, ION figures, naval figures, Kriegsmarine
Figures (resin)
New Arrivals
16/04/2024
£13.80
£12.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
Kneeling pilot for the North-American F-86F-40 Sabre (designed top be used with Hasegawa kits) but will be suitable for many other 1950's USAF aircraft
Figures (resin)
New Arrivals
16/04/2024
£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
Mitsubishi A6M Zero pilot standing
Figures (resin)
New Arrivals
16/04/2024
£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
Pilot F-86 Sabre
Figures (resin)
New Arrivals
16/04/2024
£9.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:48
Crew for the de Havilland Mosquito Mk.IV/PR Mk.IV/Mk.VI/PR.XVI / B.Mk.XVI (designed to be used with Airfix, Frog, Matchbox, Revell and Tamiya kits)
Figures (resin)
New Arrivals
16/04/2024
£14.40
£12.96
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
The set includes two trailers molded from plastic, a compressor and a fuel filter made with 3D technology
Figures (resin)
New Arrivals
16/04/2024
£20.50
£18.45
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
Swedish Vampire pilots with cloth helmets.The pilots are integrated into the seats. Sold in pairs 3D-Printed de Havilland Vampire J28 (designed to be used with Pilot Replicas kits)
Figures (resin)
New Arrivals
10/04/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 Vampire pilots with MK 1A helmets. Made for Pilot-Replicas Vampire kit with MB seats. Sold in pairs 3D-Printed de Havilland Vampire T.1 (designed to be used with Pilot Replicas kits)
Figures (resin)
New Arrivals
10/04/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 pilots walking, late 1950s & 1960s. Sold in pair 3D-Printed de Havilland Vampire T.1 (designed to be used with Pilot Replicas kits) suitable for many other kits
Figures (resin)
New Arrivals
10/04/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
1:35
British Army Squad Leader 1970s-1980s Action Pose with SLR Rifle [British Army Ferret Scout Car Mk.2](designed to be used with Airfix kits)
Figures (resin)
New Arrivals
06/04/2024
£5.99
£5.39
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
British Army Ferret Scout Car Mk.2 Commander [wearing Parka](designed to be used with Airfix kits)
Figures (resin)
New Arrivals
26/03/2024
£5.99
£5.39
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:35
British Army Ferret Scout Car Mk.2 Crewman in Combat Dress Denims This figure is designed for use with any Ferret Scout Car Mk.2 and Saracen kits. The figure is wearing the 1950 Pattern Combat Dress Denims, that was eventually replaced by the 1968 Pattern Smock in DPM, but was still to be seen in the late 1970s. (designed to be used with Airfix kits)
Figures (resin)
New Arrivals
26/03/2024
£5.99
£5.39
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
British Army Ferret Scout Car Mk.2 Crewman, with Flak Jacket (designed to be used with Airfix kits)
Figures (resin)
New Arrivals
26/03/2024
£5.99
£5.39
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
Ferret Scout Car Mk.2 Conversation Piece Three Figure Set Ideal for your Ferret Scout Car Mk.2 display! This boxed set contains three much-loved figures from our 35th 35th British Army Figures range, at a bargain price. A soldier wearing the woolly-pully sitting on the wing of a Ferret And two leaning figures one in a pullover having a aâé-~brew and a second wearing a Parka. (designed to be used with Airfix kits...
Figures (resin)
New Arrivals
26/03/2024
£16.80
£15.12
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:35
British Army Fusilier, Northern Ireland, 1970s - Ideal Companion for a Northern Ireland Ferret Scout Car Mk.2 Display! This figure depicts a British Fusilier from the late 1970s-1980s, wearing the M.69 Flak Jacket over the standard DPM uniform. He is holding the L1A1 SLR Rifle. The M.69 Flak Jacket was worn in combat by the British Army in Northern Ireland, and in Lebanon etc. - making it an ideal companion for a display of th...
Figures (resin)
New Arrivals
26/03/2024
£5.99
£5.39
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
Faun L900 truck Figure Set (designed to be used with Das Werk kits) but should be suitable for any vehicles that need a driver and passenger figure.
Figures (resin)
New Arrivals
22/03/2024
£23.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
Dassault-Mirage 2000B/2000D/2000N (FRANCE) female pilot (in a standing position- type 1) (designed to be used with Kitty Hawk Model kits)
Figures (resin)
New Arrivals
22/03/2024
£12.50
£11.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
1:32
Dassault-Mirage 2000B/2000D/2000N (FRANCE) male pilot (in a standing position- type 1) (designed to be used with Kitty Hawk Model kits)
Figures (resin)
New Arrivals
22/03/2024
£12.50
£11.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
Dassault-Mirage 2000B/2000D/2000N (TAIWAN) male pilot (in a standing position- type 1) (designed to be used with Kitty Hawk Model kits)
Figures (resin)
New Arrivals
22/03/2024
£12.50
£11.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
Dassault-Mirage 2000B/2000D/2000N (INDIA) male pilot (in a standing position- type 1) (designed to be used with Kitty Hawk Model kits)
Figures (resin)
New Arrivals
22/03/2024
£12.50
£11.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
Dassault-Mirage 2000B/2000D/2000N (INDIA) male pilot (in a standing position- type 2) (designed to be used with Kitty Hawk Model kits)
Figures (resin)
New Arrivals
22/03/2024
£12.50
£11.25
Out of Stock
0