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 = 953 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 22 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 VA3289
RNZAF Vought F4U-1D Corsairs - Home based, Fighter Leaders School, three options - NZ5545, "Corsair", September 1944. NZ5545, was painted with a large white "Corsair" name on both sides of the fuselage for public displays throughout New Zealand. - NZ5545, Fighter Leaders School, 1945. Code letters "JZ-G". - NZ5566, Fighter Leaders School, April 1945. Code letters "JZ-M".
Aircraft decals (military)
Catalogue
£13.99
-
In Stock
8

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 VA3290
RNZAF Vought FG-1D Corsairs, No.14 Squadron. post-war in Japan, three options. - FG-1D Corsair NZ5659 "Doris", No.14 Squadron, 1947. - FG-1D Corsair NZ5660 "Gremlin", 1946. - FG-1D Corsair NZ5639 "Kathleen", 1946. FG-1Ds NZ5539, NZ5659 and NZ5660 were all painted in the US Navy all-over Glossy Sea Blue scheme but with RNZAF applied 48 inch roundels on the wings and 36 inch on the fuselage.
Aircraft decals (military)
Catalogue
£11.99
-
In Stock
9

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 VA3292
1:32
RNZAF Vought F4U-1 Corsairs #3. "Donald Duck" nose art, two options - NZ5262, No.30 SU, 1945. NZ5262 was based on on Green Island, post 21 December 1944. RNZAF records note that No.30 SU "owned" NZ5262 from 8 August 1944 until it was destroyed in a take-off crash with Flight Sergeant D.C. Howlett at the controls on 23 July 1945 at Jacquinot Bay. - NZ5277, No.21 Squadron, July 1945.
Aircraft decals (military)
Catalogue
£13.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
Ventura VA3291
RNZAF Grumman TBF-1C Avengers #2, Pacific Theatre, 1944. Two options.ƒ¢aš-é"ƒ¢aš-é" - NZ2522 TBF-1C, No.30 and 31 Squadron. "winged kiwi" ƒ¢aš-é" - TBF-1C, No.30 Squadron. "Donald Duck" nose art.
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

No Image
Ventura VA3288
RAF, RAAF 8" serials in 1/32nd scale. LIGHT GREY
Aircraft decals (military)
Catalogue
£8.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 VA3287
New Zealand civilian 'Mobil' Mustang. Two options. North-American 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.
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 VA3286
Curtiss 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 the cowlings....
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 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 VA3285
RNZAF Curtiss P-40N Warhawk s, 'Gloria Lyons' and 'Leslie' 1/32nd scale decal - P-40N Warhawk NZ3220 'Gloria Lyons' late 1944. Two versions of 'Gloria' nose art. - P-40N Warhawk NZ3259 'Leslie' June 1944. 'Leslie' , was the highest serial number RNZAF P-40 to see combat.
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 VA32503
RNZAF North-American Harvard Mk.II NZ934 WW2 yellow trainer scheme. One option. [T-6 Texan]
Aircraft decals (military)
Catalogue
£15.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 VA3283
1:32
Curtiss P-40s. American Volunteer Group P-40N Warhawk and RAAF P-40M and Ns. Package includes two sheets. One with blue and white roundels etc and the other, nose art and serials etc. Nationalist Chinese P-40N Warhawk 'Boss's Hoss' of the 3rd FG Composite Wing at Liangshan China, August 1944. RAAF, P-40M A29-325, 'Pluto' No.86 Squadron, late 1943. RAAF, BU-O 'Angry Bee' of No.80 Squadron, November 1944. RAAF P-40N Warhawk...
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

Ventura VA3284
1:32
RAAF Bristol Beaufighters, No.31 and No.93 Sqn Squadron A8-23 'tiger nose' of No.31. Note that two tiger decals for the nose are included. The second one is a spare. A8-116 'Pistol Packin' Gremlin' of No.93 Squadron, 1945. A8-116 was one of the most photographed aircraft in No.93 Squadron. Package includes two sheets. One with blue and white roundels etc and the other, nose art and serials etc.
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 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 VA3275
1:32
New Zealand Territorial Air Force "checker board" Mustangs 1/32nd scale. NZ2419 of No.2 Wellington, NZ2427 of No.3, Canterbury and NZ2430 of No.4 Otago Squadrons. Extended fuselage checks are included for the No.4 Squadron aircraft so that they may be used to build a No.4 Squadron Harvard, circa 1956.
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

Ventura VA3282
1:32
RNZAF Vought Corsairs F4U-1D NZ5485 Kohimarama IX with Green Maori Tiki nose art, 1945 Pacific Theatre and FG-1D Corsair NZ5617 with No.14 Squadron, RNZAF, Japan, 1946.
Aircraft decals (military)
Catalogue
£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

Ventura VA3281
1:32
RNZAF Harvard 1/32nd scale. Kiwi roundel, Red Checkers aerobatics team. NZ1076, Mainplane Grey and International Orange scheme, "Kiwi" roundels. May be modelled with or without Red Checker aerobatics team cowlings. [North-American T-6 Texan]
Aircraft decals (military)
Catalogue
£8.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

Ventura VA3270
1:32
British and Australian - RAF and RAAF 8 inch serial letters and numbers. Black. Three sheets in total, with a wide variety of character styles. Includes stencil style and 'ROYAL NAVY' words.
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 VA3277
1:32
Curtiss P-40N Warhawk (4) A29-607 BU-G 80 Sqn RAAF S/Ldr John Waddy; 2105202/13 7thFS/49thFG Lt De Haven New Guinea 1944; NZ3283 Sliver Barge; NZ3278 OD-22 4 OTU
Aircraft decals (military)
Catalogue
£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

Ventura VA3278
1:32
RNZAF TBS Avengers (4) NZ2518/518 30 Sqn F/O Fred Ladd `Plonky'; NZ2509 `Grumpy'; NZ2523/523; NZ2504 5 Target Towing Sqn `Popeye' with yellow tail;
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 VA3274
1:32
RNZAF Douglas SBD-5 Dauntless, Pacific Theatre, No.25 Squadron - two options. NZ5064 `He'll Be Back Again', April 1944 and NZ5060 'Donald Duck', 1944. U.S. Navy Mid-War `Three Tone Scheme'.
Aircraft decals (military)
Catalogue
£8.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

Ventura VA3273
1:32
Supermarine Spitfire Mk.V Tropical version ER187 WZ-C 309th FS North Africa
Aircraft decals (military)
Catalogue
£3.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 VA3269
1:32
RNZAF Vought F4U-1 Corsair (1) NZ5416/16 No 3 Servicing Unit `Tutae Wera'
Aircraft decals (military)
Catalogue
£8.99
-
In Stock
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