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

No Image
North-American T-28C/T-28D Trojan NAVY 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
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-23 missile with APU-68 (2 pcs) (Sukhoi Su-17, Su-24, Mikoyan Mikoyan MiG-23, Mikoyan MiG-27, Yakovlev YaK-38) (designed to be used with Hobby Boss, Kitty Hawk Model and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£10.80
-
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
Kh-23M missile with APU-68 (2 pcs)(Sukhoi Su-17, Su-24, Mikoyan MiG-23, Mikoyan MiG-27, JaK-38) (designed to be used with Hobby Boss, Kitty Hawk and Trumpeter kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£10.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))(A-6, A-10, Grumman F-14, McDonnell F-15 Eagle , F-16, F/A-18, General-Dynamics F-111, Dassault-Mirage 2000) (designed to be used with Kitty Hawk, Italeri, Hobby Boss, Tamiya. Hasegawa, Meng, Academy, Revell, Kinetic Model kits) [General-Dynamics Lockheed-Martin] (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£9.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)(A-6, A-10, Grumman F-14, McDonnell F-15 Eagle , F-16, F/A-18, General-Dynamics F-111, Dassault-Mirage 2000) Kitty Hawk, Italeri, Hobby Boss, Tamiya. Hasegawa, Meng, Academy, Revell, Kinetic [General-Dynamics Lockheed-Martin] (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£10.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
P-50 SH practice bomb (8 pcs)(Su-7, Su-17, Su-24, Su-25, Su-27, Su-33, Su-34, Su-35, MiG-21, MiG-23, MiG-27, Mikoyan MiG-29, Jak-38, Jak-130, Mi-8, Mi-24, Vi-28, Ka-27, Ka-29, Ka-30, Aero L-39, L-29) Smer, KP, Eduard kits, Trumpeter, Hobby Boss, Kitty Hawk, (designed to be used with Zvezda kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£7.50
-
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
P-50 T practice bomb (8 pcs)(Sukhoi Su-7, Su-17, Su-24, Su-25, Su-27, Su-33, Su-34, Su-35, Mikoyan MiG-21, MiG-23, MiG-27, MiG-29, Yakovlev Yak-38, Yak-130, Mil Mi-8, Mi-24, Mi-28, Kamov Ka-27, Ka-29, Ka-30, Aero L-39, L-29) (designed to be used with Eduard kits, Hobby Boss, Kitty Hawk Model, Kopro (ex KP) Smer, Trumpeter and Zvezda kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£7.50
-
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
ODAB-500PM bombs (2pcs) (Sukhoi Su-17, Su-25, Su-24, Su-33, Su-34, Su-35, Mikoyan MiG-23, MiG-27) (designed to be used with Hobby Boss, Kitty Hawk Model, Kopro (ex KP) Smer 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
CATM-114 laser guided Captive Training missile (4 pcs) (Boeing/Hughes AH-64, AH-1, UH-60, SH-60, Eurocopter Tiger, OH-58, RAH-66, MQ-1, MQ-9) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£5.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
Kamov Ka-50 (52) (all versions) wheels set (1/72) (designed to be used with Bilek, Dragon, Esci, Hobby Boss, Italeri, Tamiya and Zvezda, kits) (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£6.20
-
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 to be used with Academy, Bilek, Eduard kits, Kopro (ex KP), Italeri, R.V.Aircraft and Zvezda kits) (discontinued August 2023)
Aircraft detailing sets (resin)
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

No Image
RBK-250 PTAB-2,5M Cluster bomb (4 pcs) (Sukhoi Su-7, Su-17, Su-22, Su-24, Su-25, Su-34, Mikoyan MiG-21, MiG-27) (designed to be used with A-Model, Bilek, Eduard kits, Italeri, Modelsvit, Trumpeter and Zvezda kits) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£6.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
kh-25MP (AS-10 Karen) missile with APU-68 (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
North-American T-28 Trojan NAVY wheels set (designed to be used with Heller and Sword kits)[T-28C T-28D] (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£5.70
-
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
Polikarpov U-2/Po-2VS wheels set (designed to be used with ICM , IOM-kit and Kopro (ex KP kits) (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£4.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
1:72
Douglas B-26 (A-26) Invader type 4 wheels set (designed to be used with Italeri kits)[B-26K A-26C A-26A A-26B] (discontinued August 2023)
Aircraft wheels (resin)
Limited Availability
£7.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
P-50 Sh practice bomb (8 pcs)(Mikoyan MiG-21, Mikoyan MiG-23, Mikoyan MiG-27, Su-7, Su-17, Su-24, Su-25, Su-27, Su-30, Su-34) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£6.20
-
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
P-50 T practice bomb (8 pcs)(Mikoyan MiG-21, Mikoyan MiG-23, Mikoyan MiG-27, Su-7m, Su-17, Su-24, Su-25, Sukhoi Su-27, Su-30, Su-34) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£6.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
CATM-114 Captive Training missile (4 pcs) (Boeing/Hughes AH-64, AH-1, UH-60, SH-60, Eurocopter Tiger, OH-58, RAH-66, MQ-1, MQ-9) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£4.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
CATM-114 laser guided Captive Training missile (4 pcs) (Boeing/Hughes AH-64, AH-1, UH-60, SH-60, Eurocopter Tiger, OH-58, RAH-66, MQ-1, MQ-9) (discontinued August 2023)
Aircraft detailing sets (resin)
Limited Availability
£4.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
Sukhoi Su-57 canopy and wheels painting painting masks (designed to be used with Zvezda kits) (discontinued August 2023)
Aircraft paint masks (self adhesive)
Limited Availability
£5.99
-
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
1:48
Sukhoi Su-33 Canopy Painting Masks (designed to be used with Minibase kits) (discontinued August 2023)
Aircraft paint masks (self adhesive)
Limited Availability
£6.50
-
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
Mikoyan MiG-31 Pre-cut painting masks (designed to be used with Avant Garde kits) (discontinued August 2023)
Aircraft paint masks (self adhesive)
Limited Availability
£8.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:32
Boeing F/A-18E/F/A-18F/F/A-18G SUPER HORNET Type 1 exhaust nozzles (designed to be used with Revell kits) (discontinued August 2023)
Aircraft exhausts (resin)
Limited Availability
£21.50
-
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
McDonnell F-18 (F-18E/F-18G) SUPER HORNET Type 1 exhaust nozzles (designed to be used with Trumpeter kits) (discontinued August 2023)
Aircraft exhausts (resin)
Limited Availability
£21.50
-
In Stock
1