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
Eduard Big-Ed EBIG33118
1:32
Gloster Sea Gladiator Mk.II 2 (designed to be used with ICM kits) This BiG-Ed set includes all these Eduard sets..... ED32969 Sea Gladiator 1/32 ED33257 Sea Gladiator seatbelts STEEL 1/32 EDJX255 Sea Gladiator 1/32 (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£26.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

Eduard Big-Ed EBIG3356
1:32
Focke-Wulf Fw-190F-8 PART II (designed to be used with Revell kits) This BiG-Ed set includes all these Eduard sets.... ED32373 Fw-190F-8 wheel bay ED32374 Fw-190F-8 armament ED32375 Fw-190F-8 engine (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£30.60
-
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

Eduard Big-Ed EBIG3370
Hawker Tempest Mk.V (designed to be used with Revell and Special Hobby kits) This Big-Ed set includes these Eduard sets.... EDJX193 Tempest Mk.V ED32392 Tempest Mk.V landing flaps ED32880 Tempest Mk.V ED32881 Tempest Mk. V seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£36.70
-
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

Eduard Big-Ed EBIG3379
Lockheed-Martin F-35A Lightning II. (designed to be used with Italeri kits) This BiG-Ed set includes all these Eduard sets.... EDJX201 F-35A ED32404 F-35A exterior ED32405 F-35A wheel bays & bomb bays ED32910 F-35A interior ED33169 F-35A seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£42.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

Eduard Big-Ed EBIG3386
1:32
Polikarpov I-16 type 24 WWII Soviet Fighter (designed to be used with Hasegawa, ICM and Revell kits) This BiG-Ed set includes all these Eduard sets.... EDJX209 I-16 Type 24 ED32919 I-16 Type 24 ED33185 I-16 Type 24 seatbets STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£22.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

Eduard Big-Ed EBIG3388
1:32
North-American P-51D Mustang (designed to be with Revell kits) This BiG-Ed set includes all these Eduard sets.... EDJX210 P-51D ED32419 P-51D-5 exterior ED32420 P-51D undercarriage ED32920 P-51D-5 interior ED33187 P-51D seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£52.40
-
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

Eduard Big-Ed EBIG4809
McDonnell F-4E Phantom (designed to be used with Hasegawa kits) This BiG-Ed set includes all these Eduard sets..... ED48434 F-4 Phantom F.O.D. ED48462 F-4 Phantom exhaust ED49009 Remove Before Flight/RBF tags ED49014 F-4 Phantom ejection seat green ED49231 McDonnell F-4E Phantom EDEX004 F-4 Phantom EDFE243 F-4 Phantom placards (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£30.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

Eduard Big-Ed EBIG4818
Lockheed F-104C STARFIGHTER (designed to be used with Hasegawa kits) This BiG-Ed set includes all these Eduard sets..... ED48422 Lockheed F-104C Starfighter F.O.D. ED49009 Remove Before Flight/RBF tags ED49222 Lockheed F-104C/F-104J Starfighter EDEX031 Lockheed F-104 Starfighter EDXF544 Lockheed F-104 Starfighter surface panels (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£22.60
-
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

Eduard Big-Ed EBIG49161
1:48
Stearman PT-17 Kaydet (designed to be used with Revell kits) This BiG-Ed set includes all these Eduard sets..... EDEX529 PT-17 ED49785 PT-17 ED49788 PT-17 seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£17.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

Eduard Big-Ed EBIG49172
Sukhoi Su-27 (designed to be used with Hobby Boss kits) This BiG-Ed set includes all these Eduard sets.... EDEX546 Su-27 ED48917 Su-27 exterior ED49813 Su-27 ED49814 Su-27 seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£32.70
-
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

Eduard Big-Ed EBIG49177
1:48
Sukhoi Su-17M3/M4 'Fitter' (designed to be used with Kitty Hawk Model kits) This BiG-Ed set includes all these Eduard sets.... EDEX551 Su-17M3/M4 ED48922 Su-17M3/M4 exterior ED48923 Su-17M3/M4 F. O.D. ED48924 Su-17M3/M4 air brakes ED49829 Su-17M3/M4 interior ED49830 Su-17M3/M4 seatbelts STEEL 1/48 (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£55.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

Eduard Big-Ed EBIG49214
1:48
McDonnell RF-101C Voodoo (designed to be used with Kitty Hawk Model kits) This BiG-Ed set includes all these Eduard sets.... EDEX630 RF-101C/G/H TFace canopy frame paint mask (inside & outside) ED48978 RF-101C exterior ED49939 RF-101C interior EDFE940 RF-101C seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£36.70
-
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

Eduard Big-Ed EBIG49234
Dornier Do-217N-1 (designed to be used with ICM kits) This BiG-Ed set includes all these Eduard sets..... EDEX659 Do 217N-1 ED48997 Do 217N-1 landing flaps ED48998 Do 217N-1 exterior ED491018 Do 217N-1 EDFE1019 Do 217N-1 seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£54.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

Eduard Big-Ed EBIG49237
Lockheed F-104G Starfighter early (designed to be used with Kinetic Model kits) This BiG-Ed set includes all these Eduard sets..... ED49103 Remove Before Flight STEEL EDEX664 F-104G ED491037 F-104G early EDFE1039 F-104G early seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£25.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

Eduard Big-Ed EBIG49238
Lockheed F-104G Starfighter late (designed to be used with Kinetic Model kits) This BiG-Ed set includes all these Eduard sets..... ED49103 Remove Before Flight STEEL EDX664 F-104G ED491038 F-104G late EDFE1040 F-104G late seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£25.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

Eduard Big-Ed EBIG49248
Bell UH-1N (designed to be used with Kitty Hawk Model kits) This BiG-Ed set includes all these Eduard sets..... ED49103 Remove Before Flight STEEL EDEX680 UH-1N ED491066 UH-1N EDFE1067 UH-1N seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£23.60
-
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
Eduard Big-Ed EBIG49269
North-American B-25D Mitchell (designed to be used with Revell kits) This BiG-Ed set includes all these Eduard sets..... ED481030 B-25D exterior ED491117 B-25D EDFE1118 B-25D seatbelts STEEL EDEX714 B-25D (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£42.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
Eduard Big-Ed EBIG49279
Panavia Tornado ASSTA 3.1 (designed to be used with Revell kits) This BiG-Ed set includes all these Eduard sets..... EDEX741 Tornado ASSTA 3.1 ED481040 Tornado ASSTA 3.1 exterior ED491137 Tornado ASSTA 3.1 interior ED491138 Tornado ASSTA 3.1 undercarriage EDFE1138 Tornado ASSTA 3.1 seatbelts STEEL (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£55.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

Eduard ED17036
1:700
Chain bar railings. Universal use. No particular kit (Eduard sold out February 2023)
Ship detailing sets (etched)
Limited Availability
£17.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
Eduard ED32462
Panavia Tornado GR.4 exterior (designed to be used with Italeri kits)(Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£23.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

Eduard ED32865
1:32
Luftwaffe instruments (Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£11.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

Eduard ED33006
Messerschmitt Bf-109G-6 instrument panel PRE-PAINTED IN COLOUR! (designed to be used with Hasegawa kits)(Eduard sold out February 2023)
Aircraft detailing sets (etched)
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

Eduard ED33200
Northrop F-5E Tiger (designed to be used with Kitty Hawk Model kits)(Eduard sold out February 2023)
Aircraft detailing sets (etched)
Limited Availability
£14.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

Eduard ED35354
Pz.Kpfw.V Panther Ausf.G early (designed to be used with Tamiya kits)Pz.Kpfw.V Panther Schurzen/Schƒ¼rzen (designed to be used with Tamiya kits)(Eduard sold out February 2023)
Military vehicle 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

Eduard ED35683
Zimmerit Pz.Kpfw.VI King Tiger Sd.Kfz.182 with Porsche (designed to be used with Dragon kits)(Eduard sold out February 2023)
Military vehicle detailing sets (etched)
Limited Availability
£5.99
-
In Stock
2

[1]   «      206   |   207   |   208   |   209   |   210      »      [269]
-100 -50 -20 -10 | +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