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

Extra Tech EX14434
1:144
ATR ATR-42-500 (designed to be used with Italeri kits)
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

Extra Tech EX14436
Boeing 767-300 (designed to be used with Zvezda kits)
Aircraft detailing sets (etched)
Limited Availability
£12.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

Extra Tech EX14437
1:144
Boeing 737-800 (designed to be used with Zvezda kits)
Aircraft detailing sets (etched)
Limited Availability
£12.50
-
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

Extra Tech EX14438
Boeing 737 MAX 8 (designed to be used with Zvezda kits)
Aircraft detailing sets (etched)
Limited Availability
£12.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

No Image
Extra Tech EX14439
1:144
Boeing 777-300 ER (designed to be used with Zvezda kits)
Aircraft detailing sets (etched)
Limited Availability
£12.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

Naval Fighters NF72
No Scale
Temco TT-1 Pinto by Mark Frankel
Aircraft books
Limited Availability
£14.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

Lukgraph LUK48-05
Friedrichshafen FF.33L In German Service
Aircraft kits (resin)
Limited Availability
£116.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
Foxbot Decals FBOT48027A
Mikoyan MiG-29UB Ukranian Air Forces, digital camouflage (decals with masks) (designed to be used with Academy and Eduard kits)
Aircraft decals and paint masks (military)
Limited Availability
£27.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

Foxbot Decals FBOT48042T
Ukrainian Rooks: Sukhoi Su-25UB with Stencils
Aircraft decals (military)
Limited Availability
£19.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

Foxbot Decals FBOT72027
1:72
Mikoyan MiG-29UB Ukranian Air Forces, digital camouflage Italeri, Heller, Condor, Trumpeter kits
Aircraft decals (military)
Limited Availability
£11.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

CMK/Czech Master Kits CMF35119
Iraqi tankers/tank crew x 2 1/2 figures
Figures (resin)
Limited Availability
£22.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

Eduard ED49830
Sukhoi Su-17M3/M4 seatbelts STEEL (designed to be used with Kitty Hawk Model kits) (Eduard sold out May 2023 part 3)
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

Starfighter Decals SFD350025S
Generic USN Air Group Markings 1944-45 Now Silk screened! Printed by Cartograf. This set contains six sheets to provide markings for around 90 aircraft from US Navy Carrier Air Groups from late 1944 thru 1945. Markings for around 20 SB2C Helldivers, 20 TBM Avengers, and 70 F6F Hellcats and/or F4U Corsairs to include the Buzz numbers and National Insignia. These are the correct size unlike most of the kit markings availa...
Aircraft decals (military)
Limited Availability
£14.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

Starfighter Decals SFD48011
Stearman PT-17 Kaydets Designed for the Revell Stearman PT-17 N2S-3 Stearman Trainer. This set covers the USAAC/USAAF Aircraft markings used from 1940 to 1946 to include: 4 styles of the US Insignia; Wing Walks; Tail Stripes; Side Numbers in 3 colors; Stencils; and both PT-13 and Stearman PT-17 Data Blocks. Placement guide provided 5 suggested schemes for PT-13s, Stearman PT-17s and PT-27s (Export version used in the US ...
Aircraft decals (military)
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

Starfighter Decals SFD48014
Grumman Wildcats at Midway. For Tamiya and Hobby Boss kits. Markings for 7 different aircraft that served at the Battle of Midway. Two versions of Wildcats covered F4F-3 and F4F-4.
Aircraft decals (military)
Limited Availability
£9.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

Starfighter Decals SFD700106
Air Group 83. Markings for the entire Air Group 83 aboard the USS Essex in 1945. Works with both Trumpeter or Dragon 1/700 aircraft.
Ship decals
Limited Availability
£7.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

Starfighter Decals SFD72112
Pre-War Grumman F4F Wildcats Pt 1 (6) VF-41; VF-5; VF-71; VF-3; VF-72 (designed to be used with Hasegawa F4F-4 kits using Quickboost wings and cowl)
Aircraft decals (military)
Limited Availability
£4.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

Starfighter Decals SFD72127
AEF Showbirds Part 1. Designed for the Eduard Spad 13C.1 (Late) kit. Markings for two aircraft: 'Diamond Dick' and 'Flag Bus' Special Air Show Markings from the 94th Aero Squadron 1919 (designed to be used with Eduard kits)
Aircraft decals (military)
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

Eduard Big-Ed EBIG3218
Sukhoi Su-27 FLANKER B (designed to be used with Trumpeter kits) This BiG-Ed set includes all these Eduard sets..... ED32137 Sukhoi Su-27 Flanker B exterior ED32536 Sukhoi Su-27 Flanker B interior ED32537 Sukhoi Su-27 Flanker seat EDJX024 Sukhoi Su-27B Flanker B (Eduard sold out May 2023 part 2)
Aircraft detailing sets (etched)
Limited Availability
£30.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

Eduard Big-Ed EBIG3236
Curtiss P-40B TOMAHAWK (designed to be used with Trumpeter kits) This BiG-Ed set includes all these Eduard sets..... ED32150 Curtiss P-40B Tomahawk exterior ED32151 Curtiss P-40B Tomahawk landing flaps ED32553 Curtiss P-40B Tomahawk interior EDJX044 Curtiss P-40B Tomahawk (Eduard sold out May 2023 part 2)
Aircraft detailing sets (etched)
Limited Availability
£26.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

No Image
Eduard Big-Ed EBIG33139
Bell AH-1G late (designed to be used with ICM and Revell kits) This BiG-Ed set includes all these Eduard sets..... ED32991 AH-1G late 1/32 ED33289 AH-1G late seatbelts STEEL EDX279 AH-1G canopy frame paint mask (Eduard sold out May 2023 part 2)
Aircraft detailing sets (etched)
Limited Availability
£21.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 EBIG4891
Mikoyan MiG-3 (designed to be used Trumpeter kits) This BiG-Ed set includes all these Eduard sets..... ED48574 Mikoyan MiG-3 landiing flaps ED49388 Mikoyan MiG-3 EDEX229 Mikoyan MiG-3
Aircraft detailing sets (etched)
Limited Availability
£20.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 EBIG49185
Bell UH-1D 'Huey' (designed to be used with Kitty Hawk Model kits) This BiG-Ed set includes all these Eduard sets.... EDEX564 UH-1D ED48935 UH-1D cargo interior ED49861 UH-1D EDFE862 UH-1D seatbelts STEEL (Eduard sold out May 2023 part 2)
Aircraft detailing sets (etched)
Limited Availability
£32.70
-
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 EBIG49209
Grumman F-14D Tomcat (designed to be used with Tamiya kits) This BiG-Ed set includes all these Eduard sets.... EDEX624 F-14D ED48969 F-14D engines STEEL ED48970 F-14D exterior ED49933 F-14D 1 EDFE934 F-14D seatbelts STEEL (Eduard sold out May 2023 part 2)
Aircraft detailing sets (etched)
Limited Availability
£49.30
-
In Stock
1

[1]   «      194   |   195   |   196   |   197   |   198      »      [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