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
Yahu Models YMA7350
PZL P.24 (designed to be used with IBG kits)
Aircraft detailing sets (etched)
New Arrivals
30/04/2024
£3.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
Eduard Brassin ED648924
1:48
Back in stock! Hawker Hurricane Mk.IIB/Mk.IIC seat 3D-Printed (designed to be used with Arma Hobby kits)
Aircraft seats (resin)
New Arrivals
29/04/2024
£3.99
-
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

MPC MPC920
1:25
BACK IN STOCK!!! 1974 Plymouth Road Runner
Car kits (injection)
New Arrivals
26/04/2024
£46.99
-
In Stock
3

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
Karaya KY144-23
Embraer ERJ-170 Air France HOP! (ex-Hasegawa)
Aircraft kits (injection)
New Arrivals
26/04/2024
£30.40
£27.36
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
Karaya KY144-25
Embraer ERJ-170 Delta Connection/American Eagle (ex-Hasegawa)
Aircraft kits (injection)
New Arrivals
26/04/2024
£30.40
£27.36
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
Karaya KY144-34
Lockheed L-10A Electra
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.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
Karaya KY144-35
1:144
Lockheed L-10A Electra RAF
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.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
Karaya KY144-36
1:144
Lockheed L-10 Electra early Pan Am
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.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
Karaya KY144-37
1:144
Lockheed L-10A Electra USN/USCG
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.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
Karaya KY144-45
1:144
Dash 8 Q400 - (PLL LOT) - plastic parts AMP UA (ex-EE) + silk/digital printed decals.
Aircraft kits (injection)
New Arrivals
26/04/2024
£23.99
£21.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

Hasegawa HALT10730
Re-released! Space Shuttle Orbiter
Space kits (injection)
New Arrivals
26/04/2024
£13.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
Takom TAK01020
1:16
Otto Carius WWII German Tank Ace Limited Edition Otto Carius (27/5/192224/1/2015) was a German tank commander who served in World War II. He is considered one of the most successful tank aces of the war, with claims of destroying over 150 opposing tanks. Carius fought on both the Eastern and Western Fronts, and was awarded the Knight's Cross with Oak Leaves for bravery and leadership. Born in Zweibrƒ¼cken, Germany, in 1...
Figures (injection)
New Arrivals
26/04/2024
£19.99
£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
Takom TAK02125W
German WWII Jagdpanther G1 Early SdKfz 173 with Zimmerit Limited Edition WWII German Tank Destroyer. Zimmerit for upper hull surfaces. Hatches can be fitted opened or closed. Link & length tracks with track assembly jig. Photo-etch parts. Choice of 6 markings. This version of kit ref 2125 is without interior.
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02198
Pz.Kpfw.VI Tiger I Mid-production with Zimmerit The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Hatches can be fitted opened or closed. 2 types of link & length tracks with track assembly jig. M...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02199
Pz.Kpfw.VI Tiger I Late/Late Command w/ Zimmerit 2 in 1 The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Build two ways, Late version or Late Command version. Hatches can be fitted opened or clos...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02200
Pz.Kpfw.VI Tiger I Mid production with Zimmerit & Otto Carius figure Limited Edition The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Otto Carius figure included. Hatches can be fitted opened or ...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02200W
Pz.Kpfw.VI Tiger I Big Box Limited Edition (3 tanks 2 figures) Takom 10th anniversary 2013-2023 Tiger I special pack. Big Box includes: Pz.Kpfw.VI Tiger I Mid-production with Zimmerit (TAK02198) Pz.Kpfw.VI Tiger I Late production with Zimmerit (TAK02199) Pz.Kpfw.I Tiger I Mid production with Zimmerit & Otto Carius figure (TAK02200) 1:16 Otto Carius figure (TAK01020)
Military vehicle kits (injection)
New Arrivals
26/04/2024
£139.99
£125.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

Airfix AX07280
1:72
RNLI Severn Class Lifeboat
Ship kits, full hull (injection)
New Arrivals
26/04/2024
£36.49
£32.84
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
Begemot BT144003
Tupolev Tu-95MS 1\144 decal - for new Zvezda 1\144 Tu-95MS model - all Tu-95MSs with names of Russian cities. http://begemotdecals.ru/doc/144-003%20Tu-95MS%20inst%201.pdf http://begemotdecals.ru/doc/144-003%20Tu-95MS%20inst%202.pdf
Aircraft decals (military)
New Arrivals
26/04/2024
£19.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

Takom TAK02049
German WWII Super Heavy Tank Maus V1 Super-heavy German tank developed during World War II. The Maus was massive, weighing around 188 metric tons. Its armour was exceptionally thick, providing significant protection from enemy fire. It was armed with a 128mm main gun and a coaxial 75mm gun, offering substantial firepower. Despite its powerful weaponry and armour, its mobility was limited due to its immense weight and the resu...
Military vehicle kits (injection)
New Arrivals
26/04/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

Takom TAK02050
German WWII Super Heavy Tank Maus V2 Super-heavy German tank developed during World War II. The Maus was massive, weighing around 188 metric tons. Its armour was exceptionally thick, providing significant protection from enemy fire. It was armed with a 128mm main gun and a coaxial 75mm gun, offering substantial firepower. Despite its powerful weaponry and armour, its mobility was limited due to its immense weight and the resu...
Military vehicle kits (injection)
New Arrivals
26/04/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

Takom TAK02052
South African G6 Rhino Self-propelled Howitzer The G6 Rhino of the South African National Defence Force (SANDF) is a 155mm self-propelled howitzer developed in the late 1970s. It is based on a 6x6 wheeled chassis equipped with a 155mm howitzer capable of firing a variety of ammunition including HE-FRAG, smoke, illumination and incendiary rounds. Maximum range is 30km with standard HE-FRAG projectile and 39km with rocket-assis...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£52.99
£47.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
Blackdog BDT35260
Sd.Kfz.247 Ausf.B accessories set (designed to be used with ICM kits)
Military vehicle detailing sets (resin)
New Arrivals
25/04/2024
£25.70
£23.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
Blackdog BDT35261
German winter clothes WWII
Diorama accessories (resin)
New Arrivals
25/04/2024
£22.30
£20.07
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
Blackdog BDT35262
Sd.Kfz.2 Kettenkraftrad accessories set(designed to be used with Tamiya kits)
Military vehicle detailing sets (resin)
New Arrivals
25/04/2024
£18.20
£16.38
Out of Stock
0

[1]   «      4   |   5   |   6   |   7   |   8      »      [79]
| +10 +20 +50

Search Area Manufacturer Item Type Search

Part Number Scale Keywords Results per Page
Exact Match
Sort by:
Sort Direction:
Added Within last week
last 2 weeks
last month
last 6 months
last year