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=362427
		 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 14 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 113
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

Caracal Models CD48096
USAF C-21 (Learjet 35) A military version of the versatile Learjet 35 business jet, the C-21 has been serving the U.S. in personnel transport and medical evacuation role since the 1980s. This decal sheet provides six USAF marking options to let you build the Hasegawa Learjet 35 kit as a C-21. The options on this decal sheet are: C-21A 84-0135 12 FTW, Randolph AFB C-21A 84-0069 81 TRW, Keesler AFB C-21A 84-0087 76th AS , Ra...
Aircraft decals (military)
Limited Availability
£13.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

Caracal Models CD48158
F-18 Hornet - The Early Years The second sheet in our "The Early Years" series covers a wide variety of prototype/FSD Hornets from the early, exciting years of F-18 development. We provide markings for jets that took part in critical stages such as spin testing, weapons tests, and carrier trials. The wide selection of markings are supplemented by crew and mission markings when appropriate, different carrier name options, ...
Aircraft decals (military)
Limited Availability
£13.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
Caracal Models CD32026
US Army Boeing/Hughes AH-64D/E Apache US Army Combat-proven and constantly upgraded to adapt to the threats of modern battlefields, the AH-64 Apache is arguably the most capable combat helicopter in existence today. Our new 1/35 scale decal set is designed for the Takom kit and features markings for US Army AH-64D/E Apaches from multiple units. This comprehensive decal set features thirteen (13) marking options for US Army A...
Aircraft decals (military)
Limited Availability
£15.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

Caracal Models CD48024
McDonnell-Douglas T-45C Goshawk Part 2 (4) 165629 A/107 VT-7 Eagles; 165485 TW-1 Co's aircraft Go Navy Beat Army; or 165427 A/100 VT-7; 165609 A/167 V-7 the 150th Goshawk. All NAS Meridian MI; 165070 F/602 VT-86 NAS Pensacola; Full Maintenance Data. for the new Kinetic Model Kits kit.
Aircraft decals (military)
Limited Availability
£15.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

Caracal Models CD48051
International Northrop T-38A Our third decal sheet for the new Wolfpack 1/48 T-38 kit features markings for two NATO operators of the Talon. Accurate stencilling in Turkish & Portuguese, and unit insignia (121.Filo 'Bee' emblems for the Turkish aircraft, both color and outline EICPAC emblem for Portugese aircraft) are included. The options on this sheet are: T-38A 63-8183, Turkish Air Force, 121.Filo, 2000 T-38A 63-8121, Tu...
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

Caracal Models CD48055
Lockheed-Martin F-16C "Florida Makos" 482 FW. This sheet provides markings for the specially decorated 2014 flagship F-16C (87-0247) belonging to the 482FW based at Homestead AFB. Complete markings for the spectacular shark decoration and stencilling is provided on the decal sheet.
Aircraft decals (military)
Limited Availability
£10.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

Caracal Models CD48105
Guizhou PLA JL-9 / FTC-2000 Markings for colorful prototype JL-9 / FTC-2000 fighter trainers. Designed for the Trumpeter kit.
Aircraft decals (military)
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

Caracal Models CD48116
USMC Bell AH-1W Whiskey Cobra Until the recent introduction of AH-1Z, the "Whiskey Cobra" has been the primary attack helicopter of US Marine Corps. In this decal sheet, we provide several markings options for recent and current AH-1Ws. The 9 options on this sheet are: AH-1W BuNo. 160820, HMLA-467 (FDNY 9/11 tribute scheme) AH-1W BuNo. 162551, HMLA-267 "Stingers" AH-1W BuNo. 165328, HMLA-773 "Red Dogs" AH-1W BuNo. 165044, ...
Aircraft decals (military)
Limited Availability
£13.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

Caracal Models CD48125
Embraer EMB-312 Tucano International The EMB-312 Tucano turboprop trainer has been a monumental success story for the Brazilian aircraft industry; and has been serving many air forces for more than 30 years. Our latest sheet for the Hobby Boss kit provides accurate, high quality markings for several operators of the Tucano. The aircraft covered in this sheet are: EMB-312F Tucano 459/"312-JO", French Air Force, 2013 (with sp...
Aircraft decals (military)
Limited Availability
£13.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

Caracal Models CD48133
Embraer EMB-312 Tucano Brazilian "Esquadrilha da Fumaca" Once again, Caracal Models is the first to bring you high-quality markings for the Brazil's national aerobatics team "Smoke Squadron" - this time for the new Hobby Boss Embraer EMB-312 Tucano kit. Our decal sheet is designed to precisely fit the HB kit and provide enough generic markings to build almost any aircraft in the unit for the 2013 season.
Aircraft decals (military)
Limited Availability
£13.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

Caracal Models CD72033
Lockheed-Martin F-16C "Florida Makos" 482FW. F-16C "Florida Makos" 482FW Flagship This sheet provides 1/72 scale markings for the specially decorated 2014 flagship F-16C (87-0247) belonging to the 482FW based at Homestead AFB. Complete markings for the spectacular shark decoration and stencilling is provided on the decal sheet. [F-16CJ]
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

Caracal Models CD32009
Air National Guard Lockheed T-33A: During the end of its long and productive service, the T-33A Shooting Star served in the liaison, target towing or training roles in many Air National Guard units around the US. This limited edition sheet provides markings for the following Air National Guard units: 171st FIS, Michigan ANG 119th FIS, New Jersey ANG 178th FIS, North Dakota ANG (two variations) 123rd FIS, Oregon ANG 111th...
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

No Image
Caracal Models CD48134
Lockheed F-94A/B Starfire Multiple USAF marking options for F-94A/B early jet fighters. Designed for the Hobbycraft kits. You can use the Caracal Models Basics CDB48003 serial number sheet to build other aircraft from these units. Designed for the 1/48 scale Hobbycraft kit. Limited run of 250 sheets only. The options on this decal sheet are: F-94B 51-5433, North Dakota ANG F-94A 49-2568, 319th FIS, Korean War F-94A 49-253...
Aircraft decals (military)
Limited Availability
£13.99
-
In Stock
5+


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