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_manufacturer_id=8252 AND ps.id = 955 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		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
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 34 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
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
Ventura VA72102
1:48
24" RAF and Commonwealth code letters, WHITE. (these are 16" in 1/48th scale)
Aircraft decals (military)
Catalogue
£5.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

No Image
Ventura VA48109
BACK IN STOCK! Vought FG-1D Corsairs & Douglas C-47A Dakota, No.14 Squadron. Post war in Japan 1946-48. - Douglas C-47A NZ3505, No.40 Sqn - FG-1D Corsair NZ5659, "Doris", 1947 - FG-1D Corsair NZ5660, "Gremlin", No.14 Squadron, 1946 - FG-1D Corsair NZ5639, "Kathleen", 1946. =============ƒ¢aš-é"ƒ¢aš-é"
Aircraft decals (military)
Catalogue
£13.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

No Image
Ventura VA48112
RNZAF Vought F4U-1 Corsairs #3 . "Donald Duck" nose art Corsairs, four options. Four RNZAF Corsairs have been recorded wearing cartoons of an enraged Donald Duck and this decal sheet includes them all. - NZ5248, No.21 Squadron, May 1945 - NZ5255, No.4 SU, July 1944 - NZ5262, No.30 SU, 1945 - NZ5277, No.21 Squadron, July 1945
Aircraft decals (military)
Catalogue
£11.99
£10.79
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

No Image
Ventura VA48113
RNZAF F4U-1D Corsairs - Home based, Fighter Leaders School, four options - NZ5545, marked "Corsair" for public displays, September 1944. - NZ5545, Fighter Leaders School, 1945. Now with the code "JZ-G". - NZ5566, Code "JZ-M"., April 1945 - NZ5542, Fighter Pilots Pool. Nose number "42", 1945
Aircraft decals (military)
Catalogue
£11.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

No Image
Ventura VA48114
Black 18 inch letters for RAF, and Commonwealth This sheet includes a variety of thick and thin stroke 18 inch high letters in "rounded" and "squared" styles as used, mainly post-war, by the RAF and other air forces. In 1/32nd scale these are 12 inches high.
Aircraft decals (military)
Catalogue
£5.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

Ventura VA4852
1:48
24" inch black code letters. RAF WWII (RAF codes/RAF code letters/RAF serial numbers) This sheet includes a variety of black 24 inch letters in "rounded" and "squared" styles, as used by British, Australian, Canadian, New Zealand and other air forces in World War II and later.
Aircraft decals (military)
Catalogue
£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

No Image
Ventura VA48110
RNZAF Douglas C-47 / DC-3 Dakotas - "Kiwi" roundels, three options. Recommended kit, Monogram - NZ3547, No.42 Squadron. Parachute training configuration, 1977 - NZ3551, No.42 Squadron. VIP configuration mid 1970's - NZ3553, No.42 Squadron. VIP configuration 1978. Colour schemes. Gloss white above the fuselage window line and on vertical tail surfaces as shown on the profile. Propeller blades are black with white-...
Aircraft decals (military)
Catalogue
£13.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

No Image
Ventura VA48107
RNZAF Consolidated PBY-5 Catalinas,No.6 Sqn. RNZAF Consolidated PBY-5 Catalinas, Pacific Theatre 1943 - 1945. Four options. NZ4011, No.6 FB Sqn. Lauthala Bay Fiji, 1943. NZ4015, F/Lt Ian Scott, No.6 FB Sqn. Halavo Bay, Solomon Islands, March 1944. NZ4021, F/Lt Ian Scott, No.6 FB Sqn. Halavo Bay, March 1944. NZ4012, "The Naughty Nine" F/Lt Guy Rogers, No.6 FB Sqn. Halavo Bay 1945. All are in the U.S. Navy ...
Aircraft decals (military)
Catalogue
£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

No Image
Ventura VA48108
RNZAF Curtiss P-40s P-40M-5 NZ3072 "Wairarapa Wildcat" 1943. NZ3072 was delivered by ship to Auckland New Zealand and assembled at Hobsonville in March 1943. It suffered minor damage in New Caledonia on the ferry flight north to the Pacific war zone and was repaired by the local U.S.Army Air Service Command unit. Before handing the aircraft back to the New Zealanders, they painted their unit emblem of a black tomcat on t...
Aircraft decals (military)
Catalogue
£9.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

No Image
Ventura VA48106
New Zealand civilian 'Mobil' Mustang and RNZAF North-American P-51D Mustang, NZ2423. Three options. P-51D Mustang ZK-CCG was ex RNZAF Mustang NZ2417. It was sponsored by Mobil Oil from late 1964 until 1968. Initially the aircraft's all white colour scheme included Mobil's "winged horse" based logo and later the more modern plain "Mobil" signage. Also on the sheet is NZ2423 of No.2 (Wellington) Squadron, Territorial Air ...
Aircraft decals (military)
Catalogue
£8.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

No Image
Ventura VA48105
1:32
36 inch numbers for RAF, RN, RAAF, RCAF and RNZAF, BLACK Also used as 24 inch in 1/32nd scale.
Aircraft decals (military)
Catalogue
£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

Ventura VA4887
Re-printed! 24' Black numbers and NZ letters in rounded and square styles, Royal Navy lettering in 3 sizes. Also suitable for RAF, RN, RCAF, RAAF etc .
Aircraft decals (military)
Catalogue
£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

Ventura VA48103
Curtiss P-40s. American Volunteer Group China, RAAF and RNZAF P-40M and Ns. Five Options - RNZAF - NZ3220, 'Gloria Lyons' , RNZAF, late 1944. Two versions of 'Gloria' nose art. - American Volunteer Group, China - P-40N P-11461 'Boss's Hoss' , August 1944 - RAAF - A29-607, John Waddy, August 1944. - RAAF - A29-325, No.86 Squadron, New Guinea, late 1943
Aircraft decals (military)
Catalogue
£11.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

Ventura VA48104
RNZAF Harvard Mk.IIs. Three options. - Kiwi Roundel, Red Checkers aerobatics team. - 1941 'yellow canary' scheme. - 1942 camouflaged trainer.
Aircraft decals (military)
Catalogue
£11.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

Ventura VA48102
RAAF Bristol Beaufighter Mk.21s, Three options A8-23 of No.31 Squadron. Tiger nose decals. A8-116 of No.93 Squadron, 1945. 'Pistol packin Gremlin' . A8-72 'Ot-as-ell' of No.30 Squadron. Roundels are screen printed and all other art, including the shaded 'Pistol packing Gremlin' tail art is digitally printed. [Mk.XXI]
Aircraft decals (military)
Catalogue
£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

Ventura VA48101
1:48
RNZAF Grumman Avengers, WW2 and post-war target tug, three options NZ2522 TBF-1C, 30 Squadron 'winged kiwi' ; TBF-1C Avenger with 'Donald Duck' nose art; NZ2503, post war target tug, yellow and black striped paint scheme.
Aircraft decals (military)
Catalogue
£8.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

Ventura VA48502
1:48
Mustangs. RAAF kangaroo roundels. Two options.
Aircraft decals (military)
Catalogue
£17.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

Ventura VA48100
1:48
RNZAF Douglas SBD-4 and SBD-5 Dauntless dive bombers, four options. SBD-5 NZ5057 'HOWA-BAHT-THAT' SBD-5 NZ5061, Boxing Kangaroo SBD-4 NZ5034, March 1944 SBD-5 NZ5064, April 1944
Aircraft decals (military)
Catalogue
£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

Ventura VA4899
1:48
8 inch serial numbers - Black for US built aircraft. This sheet includes seven styles of black 8 inch numbers including stencil and non-stencil styles as used on P-40, P-47 and North-American P-51 Mustangs etc.
Aircraft decals (military)
Catalogue
£3.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

Ventura VA4895
Luftwaffe captured North-American P-51B and Supermarine Spitfire PR Mk.XI. Three options:- - P-51B T9+CK, Hannover-Wunstorf, summer 1944 - P-51B T9+CK, Paderborn, late 1944 - Spitfire PR.XI, T9+EB, July 1944
Aircraft decals (military)
Catalogue
£6.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

Ventura VA4898
1:48
RAF and Commonwealth 1919-1939 serials. 8" black and black with white outline. Between-the-wars period 1/48th scale serials, black and black with white outline. Often an aircraft used black serials on the fuselage and similar ones with white outlines on the rudder. A variety of styles are included covering different manufacturers. Some of the white outline styles were 8" plus the outline while others were 8" including t...
Aircraft decals (military)
Catalogue
£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

Ventura VA4897
1:48
RNZAF Vought F4U-1D & FG-1D Corsairs, three options - F4U-1D NZ5485 "Kohimarama IX" - F4U-1D NZ5566, Fighter Leaders School - FG-1D Corsair NZ5617 with No.14 Squadron, RNZAF, Japan,
Aircraft decals (military)
Catalogue
£8.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

Ventura VA3279
1:32, 1:48, 1:72
RAF and RAAF 8' serial letters and numbers. WHITE in 1/32nd scale. Also used as as 12 inch in 1/48th and 18 inch in 1/72nd scale. Each package includes three 125mm x 200mm sheets.
Aircraft decals (military)
Catalogue
£9.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

Ventura VA4896
1:48
Swiss "Neutrality" marked North-American P-51B Mustang. P-51B-10-NA serial 42-106438 force landed at Ems-Plarenga on the 19 July 1944 with Lt. Curtis Simpson at the controls. The full Swiss red and white neutrality scheme was eventually applied as per this decal sheet.
Aircraft decals (military)
Catalogue
£6.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

Ventura VA4869
1:48
Supermarine Spitfire and XP-51 Mustang Prototypes. Sheet includes a temporary airshow number with new photo from the collection of Brendon Deere
Aircraft decals (military)
Catalogue
£5.99
-
In Stock
1

   1   |   2      »      [2]

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