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

Sukhoi Su-27UB (designed to be used with Hobby Boss kits)(Eduard sold out August 2023 part 1)
Aircraft detailing sets (etched)
Limited Availability
£13.30
-
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

Mikoyan MiG-21PF seatbelts STEEL (designed to be used with Eduard kits)(Eduard sold out August 2023 part 1)
Aircraft detailing sets (etched)
Limited Availability
£5.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

Mikoyan MiG-31B/BM (designed to be used with Trumpeter kits) Includes about 20 of the more important and prominent parts for detailing the aircraft. Most sets are also available as part of a double set for the modeller who wants even more detail. Please see the picture for the double set part number. (Eduard sold out August 2023 part 1)
Aircraft detailing sets (etched)
Limited Availability
£11.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

Yakovlev Yak-130(designed to be used with Zvezda kits)(Eduard sold out August 2023 part 1)
Aircraft detailing sets (etched)
Limited Availability
£9.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

Northrop B-2A Spirit Stealth Bomber (designed to be used with Modelcollect kits) Includes about 20 of the more important and prominent parts for detailing the aircraft. Most sets are also available as part of a double set for the modeller who wants even more detail. Please see the picture for the double set part number. (Eduard sold out August 2023 part 1)
Aircraft detailing sets (etched)
Limited Availability
£9.20
-
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

PZL TS-11 'Iskra' in Polish service vol.1
Aircraft decals (military)
Limited Availability
£5.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

PZL TS-11 'Iskra' in Polish service vol.2
Aircraft decals (military)
Limited Availability
£5.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
Mikoyan MiG-15bis / S-103 Limited Edition 48007 + Eduard accessories
Aircraft kits (injection)
Limited Availability
£53.40
£48.06
-
In Stock
10

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-15bis / LIM-2 Limited Edition 48008 + Eduard accessories
Aircraft kits (injection)
Limited Availability
£53.40
£48.06
-
In Stock
8

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
M.B. L1500A (designed to be used with Attack kits)
Military vehicle paint masks (self adhesive)
Limited Availability
£2.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

McDonnell-Douglas F/A-18C (1) 163708 NG/400 VFA-147 Argonauts CAG USS John.C.Stennis 2002
Aircraft decals (military)
Limited Availability
£17.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

1:32
Mikoyan MiG-23MF/MiG-23ML intake detailing and wheel hubs/discs (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£5.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

1:32
Mikoyan MiG-23MF/MiG-23ML pylons, parachute fairing (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£20.30
-
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
Information/inscription plates
Aircraft detailing sets (etched)
Limited Availability
£4.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
Mikoyan MiG-23MF/MiG-23ML -nose wheel bay details (designed to be used with Trumpeter kits) [MiG-23MF Flogger B MiG-23ML Flogger G MiG-23MLD]
Aircraft detailing sets (etched)
Limited Availability
£11.30
-
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
Sukhoi Su-25K/Su-25UB details for bombs, rockets and fuel tanks (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£23.20
-
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
APU-68 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.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

1:32
FAB-100 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£2.25
-
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

1:32
ZB-500 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.30
-
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
UB-8MI detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Sukhoi Su-25 Frogfoot A, Sukhoi Su-25UB Frogfoot B, Sukhoi Su-27 Flanker B and Sukhoi Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.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

1:32
KH-28L detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£3.50
-
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
UPK-23 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.99
-
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
UPK-23 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.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

1:32
S-25L detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.99
-
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
S-24B detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£3.50
-
In Stock
2