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=355162
		 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 13 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

Starfighter Decals SFD48010
1:48
N2S Kaydets Two sheets. Designed for the Revell PT-17/N2S-3 kit. Set contains markings for 2-4 aircraft. Sheet One has Numbers in Black and White in two different sizes, 4 different styles of US Insignia and stencil data. Sheet 2 has Wing and Fuselage bands in 4 different colors, Wing Walks, Bu.No. and USN Logos. Color placement sheet has 3 suggested paint schemes plus data to re-create any of the thousands of N2S Kaydet...
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

Starfighter Decals SFD44203
Atlantic Coast Hawkeyes. Sheet contains markings for 3 E-2Cs that served in Atlantic Fleet Air Wings. VAW-124 Bear Aces, CVW-8 USS Nimitz 1981 (New Artwork!) VAW-125 Tiger Tails, CVW-17 USS Enterprise, 1996. Has Nose art of a leaping tiger. VAW-126 Sea Hawks. CVW-3 USS Truman, 2005. CAG Bird. For use with the Revell of Germany 1/144 E-2C kits. Markings for 3 aircraft serving in USN Atlantic Fleet Air Wings. You ...
Aircraft decals (military)
Limited Availability
£7.50
-
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

Starfighter Decals SFD350051
1:350
Air Group 5 USS Yorktown CV5 Battle of Midway. Set contains markings for the Air Group serving abroad the USS Yorktown at the Battle of Midway. Markings included the National Insignia and Tactical Markings. Also included are markings for the Yorktown herself: Bow and stern numbers, stern nameplate, Both styles of the Deck markings in the correct gray color (Dashes and lines depending on the photographs you look at). Prov...
Aircraft decals (military)
Limited Availability
£14.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

Starfighter Decals SFD48006
1:48
16th Pursuit Group Curtiss P-36 Hawks (designed to fit the Academy and Hobbycraft P-36 kits) Markings for 7 different aircraft from the 16th PG and 24th, 29th, and 43rd Pursuit Squadrons are contained on this sheet to include: 38-49 16PG CO; 38-52 and 38-2 of the 24th PS; 38-60 and 38-? of the 29PS; and 38-? of the 43rd PS Each has a color profile for decal placement and painting.
Aircraft decals (military)
Limited Availability
£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

No Image
Starfighter Decals SFD72163
Early Mitchells: North-American B-25B/C/D in the Pacific Recommended Kit: Airfix B-25B or B-25C/D. Markings for 5 B-25B, 4 Doolittle Raiders (Ruptured Duck-the original version, Whiskey Pete; TNT; and Doolittle's bird) and an ASW aircraft, 1 B-25C, and 3 B-25Ds Enough decals for 3 aircraft. Color placement guide included.
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

Starfighter Decals SFD350025S
Generic USN Air Group Markings 1944-45 Now Silk screened! Printed by Cartograf. This set contains six sheets to provide markings for around 90 aircraft from US Navy Carrier Air Groups from late 1944 thru 1945. Markings for around 20 SB2C Helldivers, 20 TBM Avengers, and 70 F6F Hellcats and/or F4U Corsairs to include the Buzz numbers and National Insignia. These are the correct size unlike most of the kit markings availa...
Aircraft decals (military)
Limited Availability
£14.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

Starfighter Decals SFD48011
Stearman PT-17 Kaydets Designed for the Revell Stearman PT-17 N2S-3 Stearman Trainer. This set covers the USAAC/USAAF Aircraft markings used from 1940 to 1946 to include: 4 styles of the US Insignia; Wing Walks; Tail Stripes; Side Numbers in 3 colors; Stencils; and both PT-13 and Stearman PT-17 Data Blocks. Placement guide provided 5 suggested schemes for PT-13s, Stearman PT-17s and PT-27s (Export version used in the US ...
Aircraft decals (military)
Limited Availability
£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

Starfighter Decals SFD48014
Grumman Wildcats at Midway. For Tamiya and Hobby Boss kits. Markings for 7 different aircraft that served at the Battle of Midway. Two versions of Wildcats covered F4F-3 and F4F-4.
Aircraft decals (military)
Limited Availability
£9.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

Starfighter Decals SFD700106
Air Group 83. Markings for the entire Air Group 83 aboard the USS Essex in 1945. Works with both Trumpeter or Dragon 1/700 aircraft.
Ship decals
Limited Availability
£7.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

Starfighter Decals SFD72112
Pre-War Grumman F4F Wildcats Pt 1 (6) VF-41; VF-5; VF-71; VF-3; VF-72 (designed to be used with Hasegawa F4F-4 kits using Quickboost wings and cowl)
Aircraft decals (military)
Limited Availability
£4.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

Starfighter Decals SFD72127
AEF Showbirds Part 1. Designed for the Eduard Spad 13C.1 (Late) kit. Markings for two aircraft: 'Diamond Dick' and 'Flag Bus' Special Air Show Markings from the 94th Aero Squadron 1919 (designed to be used with Eduard kits)
Aircraft decals (military)
Limited Availability
£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

Starfighter Decals SFD350022
1:350
Air Group 84 Early 1945. Decal set contain markings for around 90 aircraft including the G markings, numbers, and National Insignia for: VF-84 flying Vought F4U-1D, F6F-5N Corsair, and Grumman F6F-5P VMF-221 flying F4U-1D VFM-451 flying F4U-1D VB-84 flying SB2C-4E VT-84 flying Grumman TBM -3. Can be used with any of the 1/350 aircraft from Trumpeter, Hasegawa, Cyber Hobby, and various resin aircraft.
Aircraft decals (military)
Limited Availability
£15.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

Starfighter Decals SFD48012
1:48
Curtiss P-40B/P-40C/P-40G in USAAC Service. Designed for the 1/48 P-40B/C kit from Airfix and can be used on the Monogram/Revell kit as well. Set contains markings for 7 different aircraft. [Mk.II]
Aircraft decals (military)
Limited Availability
£11.99
-
In Stock
1


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