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.product_category_id = 108 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE(P.arrival_date) desc, P.code
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



Found 6,710 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
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

1:32
Hawker Hurricane Mk.I/Mk.IIC RAF National Insignia paint masks
Aircraft paint masks (self adhesive)
Limited Availability
£7.40
-
In Stock
1

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:32
North-American P-51D Mustang RAF National Insignia paint masks (most masks suitable for many other aircraft) (designed to be used with Revell, Tamiya and Trumpeter kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.75
-
In Stock
2

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:32
Bell P-39 Airacobra RAF (designed to be used with Special Hobby kits) [P-39Q P-39Q/N P-39N]
Aircraft paint masks (self adhesive)
Limited Availability
£6.75
-
In Stock
2

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
National Insignia paint mask for Bell P-39 Airacobra RAF (most masks suitable for many other aircraft.
Aircraft paint masks (self adhesive)
Limited Availability
£3.60
-
In Stock
1

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
Messerschmitt Bf-109G-10 (designed to be used with Revell RV4888 kits)*
Aircraft paint masks (self adhesive)
Limited Availability
£5.75
-
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

Military Illustrated Modeller (issue 82) February '18 (AFV Edition) 4 NEWS Military model product news 6 SUBSCRIBE AND RECEIVE A FREE TOOL SET! A very special MiM subscription offer! 8 TROUBLE IN PARADISE Takom's 1:35 Soviet ZSU-57-2 in detail 16 BON VIVANTE, EN COULEUR! Painting WW2 French Army camouflage on Tamiya's superb 1:35 Somua S35 22 BOOST YOUR COVERING POWER! Sparmax GP-850 spray-gun 24 ORDNANCE DEPOT...
Magazines
Limited Availability
£6.75
-
In Stock
2

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
Military Illustrated Modeller (issue 114) March 2021 (AFV Edition) 4 NEWS Military modelling news 6 TIGER IN THE SNOW A perfect muddy scene for Rye Field Model's 1:35 Tiger I 16 TIGER KING: MAYBACH, MODELLING, AND MUD! Tamiya's 'Zimmerit Coating Sheet' gets applied to Zvezda's Tiger II Ausf. B 26 MERKAVA MK.4/4 Merkava Mk.4/4 LIC w/Nochri-Kal Mine Roller System, previewed COVER STORY 28 DRONE KILLER: THE STORY O...
Magazines
Limited Availability
£6.75
-
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
No Scale
Military Illustrated Modeller (issue 116) May 2021 (AFV Edition) 'Mine Resistant Ambush Protected' - Building the Oshkosh M-ATV in 1:35. 4 NEWS Military modelling news 6 KETTENKRAD KITTED AFRESH! Tamiya's all-new 1:35 Sd.Kfz.2 Kettenkraftrad, previewed 8 MARCH OF THE IRON SOLDIER A extraordinary dioramic tribute to the M4 Sherman tank in 1:72 18 IS IT A BOAT, IS IT A TANK? Bronco Models 1:35 'Land-Wasser-Schl...
Magazines
Limited Availability
£6.75
-
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
Czech Air Force Pilot for L-39/L-59/L-159 (designed to be used with Avant Garde , Eduard kits, HPH, Planet Models, Special Hobby and Trumpeter kits) [Aero L-39ZA L-39C L-39ZO/ZA Albatros L-39MS/L-59 Super Albatros L-159A Alca]
Figures (resin)
Limited Availability
£9.70
-
In Stock
1

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

Tupolev Tu-22KD/Tu-22UD Blinder (designed to be used with Micro-Mir kits) (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£9.60
-
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

Grumman F6F-5 Hellcat Land based wheels set (designed to be used with Airfix kits) (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£15.80
-
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
GBU-24 (A-B) Bomb (2 pcs))(Grumman A-6 A-10, Grumman F-14, McDonnell F-15, F-16, F/A-18, General-Dynamics F-111, Dassault-Mirage 2000) (designed to be used with Academy, Hasegawa, Kitty Hawk Model, Revell, Trumpeter and Tamiya kits) [General-Dynamics Lockheed-Martin] (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£13.90
-
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
GBU-24 (B) Bomb (2 pcs)(Grumman A-6, A-10, Grumman F-14, McDonnell F-15, F-16, F/A-18, General-Dynamics F-111, Dassault-Mirage 2000) (designed to be used with Academy, Hasegawa, Italeri, Kitty Hawk Model, Revell, Trumpeter and Tamiya kits) [General-Dynamics Lockheed-Martin](discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£14.70
-
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

No Image
Avro Cierva C.30A wheels set (designed to be used with MiniArt kits) (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£11.00
-
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

No Image
R-3R missile (4 pcs) (Mikoyan MiG-21, MiG-23) (designed be used with Academy, Eduard kits, Italeri, Revell and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£9.20
-
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
1:48
RBK-500-375 é...¾-10 Cluster bomb (2 pcs) (Sukhoi Su-7/Su-17/Su-22/Su-24/Su-25/Su-34, Kikoyan MiG-21, MiG-27) (designed to be used with Eduard kits, Hobby Boss, Kopro (ex KP), Kitty Hawk Model and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£9.00
-
In Stock
2

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
RBK-250 PTAB-2,5M Cluster bomb (4 pcs) (Sukhoi Su-7/Su-17/Su-22/Su-24/Su-25/Su-34, Kikoyan MiG-21, MiG-27) (designed to be used with Eduard kits, Hobby Boss, Kopro (ex KP), Kitty Hawk Model and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£8.60
-
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
RBK-250-275 AO-1 Cluster bomb (4 pcs) (Sukhoi Su-7/Su-17/Su-22/Su-24/Su-25/Su-34, Kikoyan MiG-21, MiG-27) (designed to be used with Eduard kits, Hobby Boss, Kopro (ex KP), Kitty Hawk Model and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£8.60
-
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
1:48
SAB-250-200 bomb (4 pcs) (Sukhoi Su-7/Su-17/Su-22/Su-24/Su-25/Su-34, Mikoyan MiG-21, MiG-27) Yakovlev Yak-38, Jak-130 (designed to be used with Eduard kits, Hobby Boss, KP, Kitty Hawk and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£8.60
-
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
R-23R missile (2 pcs) Mikoyan MiG-23 Eduard kits, Italeri, Trumpeter (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£7.90
-
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

No Image
R-23é missile (2 pcs) Mikoyan MiG-23 (designed to be used with Eduard kits, Italeri and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£8.99
-
In Stock
2

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
Kh-25MR (AS-10 Karen) missile with APU-68 (4 pcs) (Mikoyan MiG-23, Mikoyan MiG-27, Sukhoi Su-17, Sukhoi Su-24, Sukhoi Su-25) (designed to be used with Kopro (ex-KP), Smer and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£10.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

No Image
Kh-25MP(AS-10 Karen) missile with APU-68 (4 pcs) (Mikoyan MiG-23, Mikoyan MiG-27, Sukhoi Su-17, Sukhoi Su-24, Sukhoi Su-25) (designed to be used with Kopro (ex-KP), Smer and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£10.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

No Image
North-American T-28A/T-28B Trojan NAVY wheels set (designed to be used with Avant Garde Models, Monogram, Revell and Roden kits) (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£7.60
-
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
North-American T-28A/T-28B Trojan Land based wheels set (designed to be used with AMG, Monogram, Revell and Roden kits) (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£7.60
-
In Stock
4