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

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
1:48
Grumman F4F-4 Wildcat late 1/48 The ProfiPACK edition kit of the US carrier based WWII fighter plane F4F-4 Wildcat late in 1/48 scale. - plastic parts: Eduard- marking options: 6- decals: Eduard- PE parts: yes, pre-painted- painting mask: yes- resin parts: no 1 / 6 F4F-4 Wildcat late 1/48 - COL A Lt.(Jg) William E. Eder, VGF-29, USS Santee (CVE-29), November 1942 2 / 6 F4F-4 Wildcat late 1/48 - COL B BuNo.11992, Ens. Ler...
Aircraft kits (injection)
New Arrivals
28/03/2024
£28.65
£25.79
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
Fokker E.III EDUARD-WEEKEND The Weekend edition kit of German WWI fighter aircraft Fokker E.III in 1/48 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: no - resin parts: no 1 / 4 Fokker E. III 1/48 - COL A E339/16, Lt. Josef Jacobs, Fokkerstaffel West, June 1916 2 / 4 Fokker E. III 1/48 - COL B E105/15, Ernst Udet, KeK Habsheim, 1916 3 / 4 Fokker E. III 1/48 - COL C...
Aircraft kits (injection)
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
Lockheed-Martin F-35B Lightning II (designed to be used with Trumpeter kits) This BiG-Ed set includes all these Eduard sets.... ED32886 Remove Before Flight STEEL ED321016 F-35B ED33364 F-35B seatbelts STEEL EDJX322 F-35B 1/32 paint mask
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£31.99
£28.79
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
Hawker Hurricane Mk.IIc (designed to be used with Hobby Boss kits) This BiG-Ed set includes all these Eduard sets.... ED481128 Hurricane Mk.IIc landing flaps ED491411 1/48 Hurricane Mk.IIc EDFE1412 Hurricane Mk.IIc seatbelts STEEL EDEX1000 Hurricane Mk.IIc TFace canopy frame paint mask (inside and outside)
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£28.65
£25.79
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
Republic P-47D-25 Thunderbolt (designed to be used with MiniArt kits) This BiG-Ed set includes all these Eduard sets.... ED491407 P-47D-25 1/48 EDFE1408 P-47D-25 seatbelts STEEL 1/48 EDEX997 P-47D-25 1/48
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£19.60
£17.64
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 (designed to be used with Trumpeter kits) This BiG-Ed set includes all these Eduard sets.... ED491409 Fulmar Mk.I 1/48 EDFE1410 Fulmar Mk.I seatbelts STEEL 1/48 EDEX999 Fulmar Mk.I TFace canopy frame paint mask (inside and outside) 1/48
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£28.65
£25.79
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
USS Nimitz CVN-68 PART II (designed to be used with Trumpeter kits) This BiG-Ed set includes all these Eduard sets.... ED53298 USS Nimitz CVN-68 part 4 1/350 ED53299 USS Nimitz CVN-68 part 5 1/350 ED53300 USS Nimitz CVN-68 part 6 1/350 ED53301 USS Nimitz CVN-68 part 7 1/350
Ship detailing sets (etched)
New Arrivals
28/03/2024
£92.99
£83.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
Jagdpanzer 38(t) Hetzer 1/35 (designed to be used with Takom kits)
Military vehicle detailing sets (etched)
New Arrivals
28/03/2024
£18.20
£16.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
Lockheed U-2R Dragon SPACE 1/48 (designed to be used with Hobby Boss kits)
3D printed decal
New Arrivals
28/03/2024
£10.30
£9.27
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
Mikoyan MiG-29K SPACE 1/48 (designed to be used with Hobby Boss kits)
3D printed decal
New Arrivals
28/03/2024
£10.30
£9.27
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
Republic P-47D-30 Thunderbolt SPACE 1/48 (designed to be used with MiniArt kits)
3D printed decal
New Arrivals
28/03/2024
£10.30
£9.27
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
Avro Lancaster B Mk.III Dambuster SPACE 1/48 (designed to be used with Hong Kong Models kits)
3D printed decal
New Arrivals
28/03/2024
£18.20
£16.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
Mikoyan MiG-21PFM grey SPACE 1/48 (designed to be used with Eduard kits)
3D printed decal
New Arrivals
28/03/2024
£12.85
£11.57
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
Mikoyan MiG-21PFM turquoise SPACE 1/48 (designed to be used with Eduard kits)
3D printed decal
New Arrivals
28/03/2024
£12.85
£11.57
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
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
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
Avro Lancaster B Mk.III Dambuster undercarriage 1/48 (designed to be used with Hong Kong Models kits)
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£22.80
£20.52
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
Avro Lancaster B Mk.III Dambuster landing flaps 1/48 (designed to be used with Hong Kong Models kits)
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£61.40
£55.26
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
Avro Lancaster B Mk.III Dambuster bomb bay 1/48 (designed to be used with Hong Kong Models kits)
Aircraft detailing sets (etched)
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
Avro Lancaster B Mk.III Dambuster radiators 1/48 (designed to be used with Hong Kong Models kits)
Aircraft detailing sets (etched)
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
Republic P-47D-30 Thunderbolt 1/48 (designed to be used with MiniArt kits)
Aircraft detailing sets (etched)
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
Avro Lancaster B Mk.III Dambuster cockpit 1/48 (designed to be used with Hong Kong Models kits)
Aircraft detailing sets (etched)
New Arrivals
28/03/2024
£18.20
£16.38
Out of Stock
0