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

Aviattic ATT48035
1:48
WWI German 'Naval Hex' Hand Painted Linen (Clear Decal Paper)
Aircraft decals (military)
Limited Availability
£16.00
-
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

Berna Decals BER14411
1:144
North-American T-6G French in Algeria: 51-14933 of EALA 3/1 'Air Pluto & Wasp' in 1961, 51-14718 of EALA 19/72 'Black Scorpion' in 1960, 49-2982 & 51-14839 of EALA 15/72 'Osprey' in 1957 & 1959, 51-15141 of EALA 16/72 'Viper' in 1959
Aircraft decals (military)
Limited Availability
£8.10
-
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

Berna Decals BER14426
1:144
Curtiss H-75 (P-36) Aces of GC I/5: S/C FranƒÆ'éois Morel Né°199, Lt Edmond Marin la MeslƒÆ'Ë e Né°217, Lt Michel Dorance Né°203, Lt Hubert Boitelet and S/C Maurice Tallent Né°147.
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

Berna Decals BER32022
1:32
Grumman F6F-5 Hellcat Armeĩ de l'air in indochina (3 schemes)
Aircraft decals (military)
Limited Availability
£11.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

Berna Decals BER32047
1:32
Morane-Saulnier MS.406C1 GC III/7 'La Furie': Lt RenƒÆ'Ë Challe nƒ¢Ë ...¾ 813 code 6 - 02/1940, A/C Jean Bertrand nƒ¢Ë ...¾ 795 code 5 - 02/1940
Aircraft decals (military)
Limited Availability
£15.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

Berna Decals BER32062
1:32
African Air Forces Mikoyan MiG-29 : MiG-29 ERAF 508, MiG-29UB ERAF 501 Eritrea; MiG-29SEh 614 & 623 Sudan.
Aircraft decals (military)
Limited Availability
£12.20
-
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

Berna Decals BER48032
Morane-Saulnier Vanneau (4) MS472 CERO 302 1953; MS475 CERO 305 1953; MS474 10S Hyeres 1951; MS474 56S Agadir 1952
Aircraft decals (military)
Limited Availability
£8.10
-
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

Berna Decals BER48036
1:48
North-American T-6G Texan/North-American SNJ-4 Texan/Harvard (4) 28227 EALA 11/72 1957; 14811/13 EALA 1/320 1958; 49-2916 EALA 03/10 1961; 5.S.20 Escadrille 5S 1965
Aircraft decals (military)
Limited Availability
£9.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

Berna Decals BER48063
1:48
Morane-Saulnier MS.406C1 : Patureau-Mirand n 449, Clausse 988, Le Guennec 658, C.34 aeronavale 06/1940 and SR 41 Vichy 1941 both 1059
Aircraft decals (military)
Limited Availability
£12.20
-
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

Berna Decals BER48077
1:48
Dassault-Mirage 2000 DGA EV (flight trials) : Mirage 2000B N 501 & Mirage 2000D N 676
Aircraft decals (military)
Limited Availability
£11.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

Berna Decals BER48108
1:48
North-American T-28S French in Algeria : 51-7812 né°19 & 52-1199 né°13 of EALA 03/009 (12/72) Bone 1961, 51-3569 né°69 of EALA 03/005 Mecheria 1961, 51-3584 né°95 & 52-1231 né°20 of EALA 03/009 (7/72) Bone 1961, 51-3513 né°51 of EALA 03/004 Telergma 1961, & 51-3488 né°40 of EALA 03/010 Batna 1961
Aircraft decals (military)
Limited Availability
£12.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

Berna Decals BER48132
1:48
Morane-Saulnier MS.406C1 GC III/7 'La Furie': Lt RenƒÆ'Ë Challe nƒ¢Ë ...¾ 813 code 6 - 02/1940, A/C Jean Bertrand nƒ¢Ë ...¾ 795 code 5 - 02/1940, Cne Bouvarre nƒ¢Ë ...¾ 806 code 1 - 04/1940
Aircraft decals (military)
Limited Availability
£14.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

Berna Decals BER48136
1:48
Dassault-Mirage IIIE Armeƒ© de l'Air Part 1 : 2-LA Né°426 - EC 2/2 'Alsace' - BA 102 Dijon 1983, 3-XJ Né°527 - EC 3/3 'Ardennes' - BA 133 Nancy 1994, 13-QC Né°410 - EC 1/13 'Artois' - BA 132 Colmar 1985.
Aircraft decals (military)
Limited Availability
£12.20
-
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

Berna Decals BER48144
Curtiss H-75 (P-36) Aces of GC II/5: Cdt Albert Petitjean-Roget Né°202, S/C AndrƒÆ'Ë Legrand Né°1 coded 4, Lt Robert Huvet Né°240 coded 1, S/Lt Pierre VillacƒÆ'aâé-que Né°208 coded 7
Aircraft decals (military)
Limited Availability
£12.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
Berna Decals BER48145
Sud Aviation Alouette III SA 319 B Armeƒ© de l'Air (Air Army): Né°2261 code 82-PZ - ETOM 82 "Maine" - 1984, Né°2234 code 58-CZ - ETOM 58 "Antilles" - 1997, Né°2077 code 341-EF - CIEH 00.341 "Maurienne" - 1983, Né°2124 code 67-JG - EH 5/67 "Alpilles" - 1978, Né°2101 - EH 2/67 "Valmy" - 1977, Né°2138 - EH 1/67 "PyrƒÆ'Ë nƒÆ'Ë es" - 1978.
Aircraft decals (military)
Limited Availability
£13.80
-
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

Cam Decals CAM32054
1:32
McDonnell F-4S Phantoms (1) 153832 ND/200 VF-302 Stallions NAS Miramar 1981 Overall Lt Gull Grey
Aircraft decals (military)
Limited Availability
£14.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

Cam Decals CAM32090
1:32
Phar East McDonnell F-4EJ Phantom (1) JASDF 07-8434 301 Sqn 5AW 20 Anniversary scheme 1994 with black fin and red/yellow/black trim on wings, fuselage and tail 4 sheet pkt
Aircraft decals (military)
Limited Availability
£29.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
GMU GMU48005
"BEN HUR" WWII US water tank trailer
Military vehicle kits (injection)
Limited Availability
£19.30
-
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

HGW HGW132089
1:32
GOTHA G.IV engines Daimler-Mercedes D.VIa - 160hp (PE Set) (designed to be used with Wingnut Wings kits)
Aircraft detailing sets (etched)
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

HGW HGW135503
1:35
Straps 1mm - Belts 1/35 - Accessory designed for plastic scale models of military vehicles. seatbelts from HGW Models are laser-cut so it is easy-to-use product. Set contains micro fabric belts for kits in 1/35 scale. ACCESSORY FEATURES contains micro-fabric highly detailed parts designed for plastic models of military vehicles made from printed micro-fabric material which behaves like a piece of rag textile parts are prep...
Military vehicle decals
Limited Availability
£7.85
-
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

HGW HGW135506
1:35
U.S. camouflage Netting - 1940-60 - Autumn sheet: approx. 11 x 19 cm Relief Print
Military vehicle decals
Limited Availability
£8.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

HGW HGW148806
1:48
Messerschmitt Bf-109G-10/K-4 BASIC LINE: microplastic seatbelts , etched buckles + canopy paint masks (designed to be used with Hasegawa kits)
Aircraft detailing sets (etched)
Limited Availability
£8.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

No Image
HGW HGW232915
Republic P-47D Thunderbolt Razorback OVER NEW GUINEA Pt.2
Aircraft decals (military)
Limited Availability
£11.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

No Image
HGW HGW232916
Republic P-47D Thunderbolt Razorback OVER NEW GUINEA Pt.3
Aircraft decals (military)
Limited Availability
£11.20
-
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
HGW HGW233918
Republic P-47D Thunderbolt Razorbacks 58TH OVER NEW GUINEA (1x 232918 + 2x HGW232921)
Aircraft decals (military)
Limited Availability
£24.99
-
In Stock
5

[1]   «      71   |   72   |   73   |   74   |   75      »      [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