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

CMK/Czech Master Kits CMQ48195
1:48
McDonnell F-4B/N Phantom II - Wheels. Set contains highly detailed wheels with early type of discs for naval version of F-B/N Phantom II (designed to be used with Hasegawa kits)[F-4B F-4N]
Aircraft wheels (resin)
Limited Availability
£6.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

CMK/Czech Master Kits CMQ48339
Mitsubishi J2M3 Raiden (JACK) seat (designed to be used with Hasegawa kits)
Aircraft seats (resin)
Limited Availability
£3.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
CMK/Czech Master Kits CMQ72375
Reggiane Re.2005 Main Wheels - detail wheels (designed to be used with Supermodel and Sword kits)
Aircraft wheels (resin)
Limited Availability
£3.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

Dead Design Models DDMSM48006
Mitsubishi A5M 'Claude' 3D/optical illusion paint mask for control surfaces (designed to be used with Fine Molds kits)
Aircraft paint masks (self adhesive)
Limited Availability
£7.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

Dead Design Models DDMSM48019
1:48
Mitsubishi A6M5 Zero m. 52c/63 3D/optical illusion paint mask for control surfaces (designed to be used with Hasegawa kits)
Aircraft paint masks (self adhesive)
Limited Availability
£7.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
Dead Design Models DDMSM48032
Kawanishi N1K1-J Shiden 3D/optical illusion paint mask for control surfaces (designed to be used with Tamiya kits)
Aircraft paint masks (self adhesive)
Limited Availability
£7.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

Dead Design Models DDMSM72001
Nakajima Ki-43-I Hayabusa 3D/optical illusion paint mask for control surfaces (designed to be used with Fine Molds kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.75
-
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
Dead Design Models DDMSM72041
Mitsubishi Ki-15/ C5M2 Babs 3D/optical illusion paint mask for control surfaces (designed to be used with Arii 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

No Image
Dead Design Models DDMSM72047
1:72
Mitsubishi J2M3 Raiden Control Surfaces 3D/optical illusion paint mask for control surfaces (designed to be used with Hasegawa 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

Dead Design Models DDMVM144001
Nakajima Ki-44 Shoki canopy frame paint mask (designed to be used with Fujimi kits)
Aircraft paint masks (self adhesive)
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

Dead Design Models DDMVM144011
1:144
Nakajima Ki-43 Hayabusa (2 masks included) (designed to be used with Platz 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

No Image
Dead Design Models DDMVM32005
1:32
Kawanishi N1K2-j Shiden KAI exterior canopy frame paint mask (designed to be used with Hasegawa kits)
Aircraft paint masks (self adhesive)
Limited Availability
£9.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
Dead Design Models DDMVM48086
Mitsubishi Ka-14 / 9-shi Prototype (designed to be used with Fine Molds kits)
Aircraft paint masks (self adhesive)
Limited Availability
£5.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
Dead Design Models DDMVM48111
Nakajima C6N1/C6N2 Saiun National Insignia with White Outline (designed to be used with Hasegawa kits)
Aircraft paint masks (self adhesive)
Limited Availability
£7.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

Dead Design Models DDMVM72009
1:72
Kawasaki Ki-61-Id Hien canopy paint frame mask (designed to be used with Dragon kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.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

Dead Design Models DDMVM72022
1:72
Kawasaki Ki-61-Id Hien canopy paint frame mask (designed to be used with Aoshima kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.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

Dead Design Models DDMVM72023
1:72
Kawasaki Ki-61-II KAI Hien (Fastback) canopy paint frame mask (designed to be used with Aoshima kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.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

Dead Design Models DDMVM72024
1:72
Kawasaki Ki-61-II KAI Hien (Bubbletop) canopy paint frame mask (designed to be used with Aoshima kits)
Aircraft paint masks (self adhesive)
Limited Availability
£6.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

Dead Design Models DDMVM72088
Aichi E11A Laura National Insignia paint masks (designed to be used with Fujimi kits)
Aircraft paint masks (self adhesive)
Limited Availability
£7.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

Eagle Cal EAG32052
1:32
Major Hans 'Assi' Hahn Part III Messerschmitt Bf-109F/Bf-109G (4) Bf-109G-2 II./JG 54 Russia Winter 1942-43; Bf-109G-2/R6 Wr 13949 II./JG 54 Russia Jan 1943; F-2 Wr 9553 Yellow 9 Oblt. Siegfried Schnell 9./JG 2 France 1941; F-4 White 1 Egon Mayer 7./JG 2 France 1941
Aircraft decals (military)
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

Eagle Cal EAG32064
1:32
Messerschmitt Bf-109F-4 Graf and Grislawski 9/JG 52 Pt 1 (4) Graf Yellow 1+~ 2 versions; Grislawski Yellow 4+~ replaced with Yellow 5+~ May 1942. All RLM 74/75/76 with yellow nose and fuselage band.
Aircraft decals (military)
Limited Availability
£17.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

Eagle Cal EAG32089
1:32
Focke-Wulf Fw-190A Jagdgeschwader JG 300 Part 2 (4) Red 3 5/Jagdgeschwader JG 300 Focke-Wulf Fw-Konrad Bauer; Yellow 18 6/Jagdgeschwader JG 300 ; Red 21 6/Jagdgeschwader JG 300 ; Yellow 1 6/Jagdgeschwader JG 300 Lt Klaus Bretschneider. All basically RLM 74/75/76
Aircraft decals (military)
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

Eagle Cal EAG32165
1:32
Focke-Wulf Fw-190F "Black 5" W.Nr. Unknown Luftflotte 4 April 1945 Frankfurt am Main, Germany Fw-190F-8. Pilot: Unknown. "Black 2" Werknummer Unknown Luftflotte 4 Fw-190F-9. Pilot: Unknown. "Green 3" W.Nr. 588717 St.II/SG 2 1945 Milovice, Czechoslovakia Arado built Fw-190F-8. Pilot: Unknown. "Red 2" Werknummer Unknown Luftflotte 4 Fw-190F-8. Pilot: Unknown.
Aircraft decals (military)
Limited Availability
£21.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

Eagle Cal EAG48051
1:48
Major Hans 'Assi' Hahn Part II Messerschmitt Bf-109F (4) F-2 Wr 5749 III./JG 2 Mid 1941 (2 schemes); F-4 Wr 7183 III..JG 2 France Sept 1941; F-4 Oblt. Puchlinger III./JG 2 Summer 1941; All St Pol France
Aircraft decals (military)
Limited Availability
£14.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

Eagle Cal EAG48066
1:48
Messerschmitt Bf-109E-1/Bf-109G-2 Graf and Grislawski 9/JG 52 Part 3 (4) All Graf. E-1 Black 17+- 2/Erg.JGr 1940 RLM 02/70/65; Yellow 9+~ two versions, one with yellow nose RLM 74/75/65; Bf-109G-2 Yellow 1 Russia 1942 RLM 74/75.76 yellow lower nose.
Aircraft decals (military)
Limited Availability
£14.99
-
In Stock
4

[1]   «      13   |   14   |   15   |   16   |   17      »      [269]
-10 | +10 +20 +50 +100

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