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 ps.id = 957 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 15 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

No Image
Caracal Models CD144020
Strategic Air Command Boeing B-52G/H Stratofortress Multiple marking options for Strategic Air Command B-52G/H bombers. The B-52 Stratofortress has been a significant figure in the United States Air Force (USAF) since the mid-1950s. With its powerful presence and remarkable payload capacity, the B-52 has been a critical player in strategic bombing missions throughout the Cold War and beyond. Despite its lengthy service life,...
Aircraft decals (military)
Catalogue
£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

No Image
Caracal Models CD144007
USAF Convair B-58 Hustler A 1/144 version of our best-selling B-58 decal sheet, with 19 options fully covering the operational career of the jet. B-58 Hustler The B-58 is more than the first Mach 2-capable operational supersonic bomber : with its sleek lines,it is a timeless aviation classic. Our latest decal sheet covers the history of B-58 operations at a level of detail and coverage that this magnificent aircraft deserves....
Aircraft decals (military)
Catalogue
£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

Caracal Models CD144021
Re-printed! Douglas C-133 Cargomaster: The C-133A Cargomaster was the first turboprop strategic airlifter for the USAF. The USAF Cargomaster fleet served between 1957 and 1971, and was primarily tasked with transporting ICBMs and other large loads that could not be carried by other transport aircraft. Designed for the new Roden kit, this 1/144 sheet provides 12 marking options for USAF and civilian Cargomasters. The options o...
Aircraft decals (military)
Catalogue
£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 CD144004
Re-printed! Boeing C-135 Family General Purpose Markings. This decal sheet provides a comprehensive selection of general-purpose markings to allow you to build and improve your 1/144 scale Minicraft C-135 series kits. While the sheet does not provide complete markings for any one particular aircraft, the wide variety of markings on this sheet will help you replace the inaccurate and generally poorly printed kit decals, and bui...
Aircraft decals (military)
Catalogue
£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

No Image
Caracal Models CD144025
Strategic Air Command Rockwell B-1B Lancer This 1/144 scale set for the B-1B Lancer features markings for aircraft from the early Strategic Air Command years of the B-1B operations. We provide options for 14 (fourteen) camouflaged Lancers, each with unique and colorful nose art. The aircraft featured on this sheet are: B-1B 86-0095 "Mystique", 28th BW, Ellsworth AFB B-1B 83-0070 "7 Wishes", 96th BW, Dyess AFB B-1B 85-0079...
Aircraft decals (military)
Catalogue
£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 CD144026
Boeing C-135 Recon Variants: This new 1/144 item is basically a scaled-down version of our very popular RC-135/WC-135 sheet. While it provides many interesting USAF recon/weather C-135 variants that you can model by converting your Minicraft kits, it also features several RC-135V/W options that you can use on your new Roden RC-135W kit out of the box. The KC-135 Stratotanker was not only a tanker - in its reconnaissance varian...
Aircraft decals (military)
Catalogue
£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 CD144019
Douglas C-9 Nightingale / Skytrain II: The C-9 Nightingale (Skytrain II in US Navy service) was a militarized version of the ubiquitous DC-9 airliner, and served the USAF and US Navy with distinction as a medical evacuation aircraft. Designed for the classic Airfix DC-9-30 kit, this decal sheet features markings for both services. The options on this decal sheet are: C-9B Skytrain II "City of Roswell" BuNo.161530, VR-46, US ...
Aircraft decals (military)
Catalogue
£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 CD144017
Boeing C-17 Globemaster III. The C-17 Globemaster III is one of the most capable and flexible transport aircraft in USAF history, and is also flown by several allies. The C-17 took part in almost every major conflict that the US was a part of since its introduction to service in 1993, and most recently proved its worth once again over Iraq and Afghanistan. This comprehensive set was designed for the Revell kit, and features m...
Aircraft decals (military)
Catalogue
£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

No Image
Caracal Models CD144015
Convair B-36B Peacemaker Multiple marking options for USAF B-36 Peacemaker strategic Cold War bombers. [B-36D) The B-36 Peacemaker was a true technological marvel and the largest combat aircraft ever built at the time. As the most capable bomber in the Strategic Air Command inventory, the B-36 was a very important deterrent in the early years of the Cold War; and remained in service until replaced by another legendary aircraf...
Aircraft decals (military)
Catalogue
£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 CD144003
USAF Rockwell B-1B Lancer Multiple marking options for USAF B-1B Lancer strategic long range bombers. A scaled-down version of our very successful B-1B sheets in larger scales, this comprehensive 1/144 scale sheet features marking options for seven B-1B Lancers in the current overall gray scheme, each with unique nose art. The aircraft featured on this sheet are: B-1B 85-0073 "Dark Knight", 9th BS, Dyess AFB B-1B 86-0126 ...
Aircraft decals (military)
Catalogue
£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 CD144002
1:144
Re-printed! Lockheed C-141B Starlifter. This very comprehensive 1/144 scale sheet provides coverage for the versatile C-141 transport aircraft which served USAF with distinction. Designed for the new Roden C-141B kit, our decals also provide you with markings for a few C-141A variants in case you would like to try to convert your kit to the Vietnam War era version. The options on this decal sheet are: C-141B 67-0121, 164 AW,...
Aircraft decals (military)
Catalogue
£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 CD144005
Boeing B-52H Stratofortress: High quality markings for recent/current USAF B-52H Stratofortresses; complete with enough stencilling and national insignia to build two examples. Designed to fit the Revell and Minicraft kits. This 1/144 scale sheet provides complete markings for no fewer than 11 USAF B-52H's in the current overall gray scheme, each with unique nose art. Designedto fit both Revell and Minicraft kits. All opera...
Aircraft decals (military)
Catalogue
£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 CD144022
Convair C-131B Samaritan Multiple USAF / US Navy marking choices for the new 1/144 scale Roden kits. A military light transport aircraft derived from the Convair CV-240/340/440 series, the C-131 Samaritan served the USAF and US Navy in many different duties from the 1950s all the way to 1990. This decal sheet is designed for the Roden CV-340 kits and provides markings for both USAF and US Navy examples. The options on this de...
Aircraft decals (military)
Catalogue
£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 CD144018
RAF Lockheed C-130J Hercules Designed for the Minicraft C-130J kit, this decal sheet features three spectacular special schemes recently worn by Royal Air Force Hercules C5 (C-130J) transport aircraft to commemorate various anniversaries. The options on this decal sheet are: Hercules C5 (C-130J) ZH880, 47 Sqn 100th anniversary scheme, RAF 47 Sqn, 2016 Hercules C5 (C-130J) ZH883, Hercules 50th anniversary scheme, RAF 47 Sqn,...
Aircraft decals (military)
Catalogue
£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 CD144013
1:144
Boeing C-97/KC-97 Stratofreighter. Multiple marking options for USAF C-97 / KC-97 Stratofreighter transport & tanker aircraft.
Aircraft decals (military)
Catalogue
£13.99
-
In Stock
3


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