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

Aber ABRPP02
1:35
Engraved Plate
Military vehicle detailing sets (etched)
Limited Availability
£7.70
-
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

Aber ABRPP15
1:35
Engraved floor Plate
Military vehicle detailing sets (etched)
Limited Availability
£7.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

Model Maker Decals D48091
Mikoyan MiG-29 Polish stencils
Aircraft decals (military)
Limited Availability
£8.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

Model Maker Decals D72061
Polish F-16C NATO Tiger Meet 2015 - F-16 Tiger Demo Team Poland (this is a full decal set with all decals for the tiger scheme, nothing else is needed to do model)
Aircraft decals (military)
Limited Availability
£10.40
-
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

Model Maker Decals D72095
1:72
German Eurofighter EF-2000A Typhoon Stencils and insignias
Aircraft decals (military)
Limited Availability
£7.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
Model Maker Decals D72096
German Eurofigher Typhoon TLG 31 Spirit of Oswald Boelcke
Aircraft decals (military)
Limited Availability
£14.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

Model Maker Decals D72104
Sukhoi Su-22 Black Boar 2017 with stencils
Aircraft decals (military)
Limited Availability
£8.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
Detail & Scale DSP4812
1:48
Grumman F9F8 Cougar (for postage these are treated as kits NOT flat items. They are too big and require packing in a box)
Model aircraft plans
Limited Availability
£1.99
-
In Stock
7

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
Detail & Scale DSP4813
Grumman F11F-1 Tiger (for postage these are treated as kits NOT flat items. They are too big and require packing in a box)
Model aircraft plans
Limited Availability
£1.99
-
In Stock
6

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

Furball Aero-Design FBD48029
1:48
Gunfighter Phantoms Part I covers 11 1/48 McDonnell F-4C/F-4Ds of the 35th and 366th Tactical Fighter Wings while based at Da Nang Air Base between 1967-1971. Sized for the Academy kit; enough stencils and data are included to build two complete models. Additionally, 2 sets of markings for the SUU-23/A gun pod are included. Here's a rundown of the Phantoms that are covered: F-4D 66-8775 'CO' 366th TFW, 480 TFS. Features 'The ...
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

Furball Aero-Design FBD48030
1:48
Gunfighter Phantoms Part II (48-030) also cover 11 1/48 McDonnell F-4C/F-4Ds of the 35th and 366th Tactical Fighter Wings while based at Da Nang Airbase between 1967-1968. Sized for the Academy kit; enough stencils and data are included to build two complete models. Additionally, 2 sets of markings for the SUU-23/A gun pod are included. Here's a rundown of the Phantoms that are covered: F-4C 63-7500 "BB" 366th TFW, 390th TFS,...
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

No Image
Furball Aero-Design FBD48088
1:48
"Northrop YF-23/F-23 Black Widows" (48-088). This set features options for the 2 protype aircraft from the ATF competition in the early 1990's, as well as 14 fictional "What If" schemes. The fictional schemes represent what the F-23 might have looked like in operation service! Here's a rundown of the aircraft covered: YF-23 #1 87-0800, 1990 (Overall Gunship Gray scheme) YF-23 #2 87-0801, 1990 (Dark Ghost Gray and Light G...
Aircraft decals (military)
Limited Availability
£19.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

Kuivalainen KUID3201
1:32
Junkers Ju-88A-4 Finnish Air Force 1944 : - JK-260, JK-268, both in black/green/light blue camouflage
Aircraft decals (military)
Limited Availability
£9.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
Model Maker Decals MD48170
Jan "Donald Duck" Zumbach Planes + resin A-26 nose (designed to be used with ICM Douglas A-26 kits) PZL P.11a. 7.13, "1", 111 FIGHTER SQUADRON WARSAW, POLAND, MAY 1939. 7.13, "1", 111 ESKADRA MYSLIWSKA, WARSZAWA, MAJ 1939. MORANE MS-406 C1. No. 670, L699, "14", DAT Etampes - GC 1/55, Etampes, France, Juni 1940. No. 670, L699, "14", DAT ETAMPES - GC 1/55, ETAMPES, Francja, czerwiec 1940. CURTISS HAWK ...
Aircraft conversions (resin)
Limited Availability
£32.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

No Image
Model Maker Decals MD72170
Jan "Donald Duck" Zumbach Planes + resin A-26B nose (designed to be used with Italeri kits) PZL P.11a. 7.13, "1", 111 FIGHTER SQUADRON WARSAW, POLAND, MAY 1939. 7.13, "1", 111 ESKADRA MYSLIWSKA, WARSZAWA, MAJ 1939. MORANE MS-406 C1. No. 670, L699, "14", DAT Etampes - GC 1/55, Etampes, France, Juni 1940. No. 670, L699, "14", DAT ETAMPES - GC 1/55, ETAMPES, Francja, czerwiec 1940. CURTISS HAWK H-75A2. No....
Aircraft conversions (resin)
Limited Availability
£21.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

RS Models RSMI92245
Dornier Do-22 float plane and wheels- 4 decal versions for Yugoslavia and Greece
Aircraft kits (injection)
Limited Availability
£43.99
-
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

Artillery ARTM32001
Hawker Hurricane Mk.IIc codel letters and national insignia paint masks (designed to be used with FLY Model YLF32012 kits)
Aircraft paint masks (self adhesive)
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
Artillery ARTM32009
PZL P.11c national insignia (designed to be used with IBG Models kits)
Aircraft paint masks (self adhesive)
Limited Availability
£8.90
-
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
Artillery ARTM35001
1:35
Mask for 3Ro Italian Truck windscreen frame paint mask (designed to be used with IBG Models kits)
Aircraft paint masks (self adhesive)
Limited Availability
£5.30
-
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

Artillery ARTM72003
Mask for PZL PZL P.37 canopy frame paint mask (designed to be used with FLY kits)[PZL PZL P.37A PZL PZL P.37B]
Aircraft paint masks (self adhesive)
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
Artillery ARTM72019
Tupolev TU-128M Fiddler wheel masks (designed to be used with Trumpeter kits TU01687 andTU 01688)
Aircraft paint masks (self adhesive)
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

No Image
Artillery ARTM72023
Boeing X-32A canopy frame paint mask (designed to be used with Italeri kits)
Aircraft paint masks (self adhesive)
Limited Availability
£3.60
-
In Stock
5

[1]   «      86   |   87   |   88   |   89   |   90      »      [269]
-50 -20 -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