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=362755 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 19 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
AOA Decals AOA48022
Grumman Intruder Sandbox Redux - USN/USMC A-6E Intruders in Libya & Desert Storm For Hobby Boss 1/48 A-6E TRAM Intruder This 1/48 sheet updates and supersedes 48-007 and covers all three Intruder squadrons (now including specific aircraft) involved in the strikes against Libya in March and April 1986 as part of Operation Prairie Fire and Operation El Dorado Canyon respectively; also covered are two squadrons from 1991 in...
Aircraft decals (military)
Catalogue
£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

No Image
AOA Decals AOA48023
Grumman Intruder Airframe Data/Stencils - A-6A, A-6B, A-6E, A-6E TRAM, & KA-6D (High-Viz) For Hobby Boss 1/48 A-6 Intruder This 1/48 sheet provides the extensive A-6 Intruder airframe stencils and markings for one aircraft (A-6A, A-6B, A-6E, A-6E TRAM, or KA-6D) in the original high-visibility Navy light gull grey and white camouflage. This new updated release supersedes previous sheet 48-002 and is now expanded with a...
Aircraft decals (military)
Catalogue
£13.50
-
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
AOA Decals AOA48024
Southeast Asia Grumman Intruders (1) - USMC A-6A, A-6B, & KA-6D Intruders in the Vietnam War USMC A-6A, A-6B, & KA-6D Intruders in the Vietnam War [Decals released September 2023] For Hobby Boss 1/48 A-6A Intruder This 1/48 decal sheet updates and supersedes 48-001 and 48-006, and covers all four USMC A-6A Intruder squadrons during the Vietnam War: VMA(AW)-224 Bengals - USS Coral Sea (1971-72) VMA(AW)-225 Vikings ...
Aircraft decals (military)
Catalogue
£25.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
AOA Decals AOA48025
Southeast Asia Grumman Intruders (2) - USN A-6A, A-6B, & KA-6D Intruders in the Vietnam War. This updated 1/48 decal sheet supersedes 48-006 and now includes additional options from 32-023 (VA-65) and 32-027 (VA-75). Five Navy squadrons are covered with multiple options for each squadron. Two squadrons, VA-165 and VA-196, were tied for the most A-6 Intruder Vietnam combat cruises (5 each). Also covered is VA-95, which support...
Aircraft decals (military)
Catalogue
£26.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

AOA Decals AOASS01
1:32, 1:48
Re-printed! Devil 501 & Devil 505 - USN Grumman A-6E Intruders on the Big Screen (1/32, 1/48, & 1/72 combo special sheet) In late 1989, A-6E TRAM Intruders from VA-165 Boomers were modified to represent the earlier A-6A from VA-196 Main Battery during a fictitious 1972 USS Independence Vietnam War cruise. This small special sheet includes decals for three scales - 1/32, 1/48, & 1/72 - and includes the exterior markings to ...
Aircraft decals (military)
Catalogue
£14.50
-
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

AOA Decals AOA48013
Re-printed! McDonnell F-4B/F-4J PHANTOM AIRFRAME DATA (Stencil Type) F-4B & F-4J Panels & Markings For Academy 1/48 F-4B & F-4J Phantom This sheet provides the extensive F-4 Phantom airframe data (including panel numbers/labels) for either an F-4B or F-4J. The airframe data provided is the painted (open stencil) type of markings commonly seen in the mid/late 1960's into the early 1970's on reworked USN/USMC F-4B and F-4J...
Aircraft decals (military)
Catalogue
£18.60
-
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
AOA Decals AOA48013X
McDonnell F-4B/F-4J PHANTOM AIRFRAME DATA (Stencil Type) F-4B & F-4J Panels & Markings National Insignia are not included in this set. Designed to be used with Academy 1/48 F-4B & F-4J Phantom This sheet provides the extensive F-4 Phantom airframe data (including panel numbers/labels) for either an F-4B or F-4J. The airframe data provided is the painted (open stencil) type of markings commonly seen in the mid/late 196...
Aircraft decals (military)
Catalogue
£14.50
-
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
AOA Decals AOA48019
Black Ponies, Shamrocks, & Jarheads - USN/USMC North-American/Rockwell OV-10D Broncos in the Vietnam War Era.This extensive 1/48 decal sheet predominately covers the Navy's unique and relatively short-lived light attack squadron VAL-4 Black Ponies, allowing the option to represent one of many possible VAL-4 Broncos throughout their entire service in Vietnam from 1969-72. Also included and related to VAL-4's history is VS-41 Sh...
Aircraft decals (military)
Catalogue
£19.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
AOA Decals AOA48020
"Tally-Ho on the FAC" - USAF North-American/Rockwell OV-10D Broncos in the Vietnam War. This 1/48 decal sheet includes 19 marking options for OV-10A Broncos used by the USAF in the crucial Forward Air Controller (FAC) role during the Vietnam War. Unfortunately, however, due to their generally nondescript markings, not all the schemes included are specifically identified to their applicable assigned Tactical Air Support Squadro...
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
AOA Decals AOA48021
Airframe Data & Markings - USN/USMC/USAF "Tally-Ho on the FAC" - USAF North-American/Rockwell OV-10D Broncos in the Vietnam Wars. This combined service sheet provides complete OV-10A Bronco airframe data and markings (stencils) for one aircraft in either the original USN/USMC green over grey, Navy overall grey, or USAF FAC grey scheme. Includes multiple variations of types of cautions and warnings for USN/USMC and USAF, lan...
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

AOA Decals AOA48014R
UPDATED AND Re-printed! SPOOKS FROM THE BEACH (1) USMC F-4B/F-4J Phantoms in the Vietnam War This extensive 1/48 sheet covers six Marine fighter attack squadrons operating the F-4B and F-4J Phantom in the Vietnam War (with some additional coverage when stationed in Japan and Hawaii). Full airframe data for one aircraft, including complete panel numbers/labels, is also included with this release. Vast majority of the aircr...
Aircraft decals (military)
Catalogue
£22.50
-
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
AOA Decals AOA48018
SPOOKS FROM THE BEACH (2) USMC McDonnell F-4B Phantoms in the Vietnam War This part two 1/48 sheet covers three additional Marine fighter attack squadrons operating the F-4B in the Vietnam War. Each squadron is represented througout their time in Vietnam. Full depot airframe data for one aircraft, including complete panel numbers/labels, is also included with this release; however, some schemes provided on this sheet sti...
Aircraft decals (military)
Catalogue
£22.50
-
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
AOA Decals AOA48017
FAC & BS BOMBER OSCAR DEUCES USAF/VNAF O-2A/O-2B Skymasters in the Vietnam War. This 1/48 decal sheet includes marking options for O-2 Skymasters in the Vietnam War primarily operated by the United State Air Force (USAF) in the Forward Air Controller (FAC) role. The O-2B, the psychological warfare variant, also served in the war utilitizing its leaflet dispenser and loudspeaker, earning the nickname of "bullshit bomber". The ...
Aircraft decals (military)
Catalogue
£18.60
-
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
AOA Decals AOA48016
HMM-264 (Rein) Black Knights - USMC BAe V-8B NA, AV-8B+, Boeing CH-46E, & CH-53E on USS Bataan. This 1/48 sheet covers multiple aircraft of the composite squadron from the 26th MEU, the reinforced HMM-264 Black Knights aboard the USS Bataan (LHD-5) in 2006-2007. 7 Marking Options Included: AV-8B Night Attack Harrier (1 option) AV-8B+ Harrier (1 option) CH-53E Super Stallion (2 options, including one with "Lucky Lad...
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

AOA Decals AOA48015
VF-154 BLACK KNIGHTS USN McDonnell F-4J Phantoms in the Vietnam War & the Cold War. For Academy 1/48 F-4J Phantom This sheet covers one Navy fighter squadron - VF-154 Black Knights - when they operated the F-4J Phantom in the 1970's. 4 Marking Options Included: USS Ranger [Vietnam] (1970-71) - standard scheme USS Ranger [Vietnam] (1972-73) - CAG & standard scheme NAS Miramar (1977-78) - CAG scheme Decals al...
Aircraft decals (military)
Catalogue
£16.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

AOA Decals AOA48011
1:48
VIETNAMESE SPADS (1) VNAF Douglas AD-6/A-1H Skyraiders in the Vietnam War For Tamiya 1/48 A-1H/A-1J Skyraider Part 1 of the South Vietnam Air Force (VNAF) Skyraider coverage, this 1/48 decal sheet includes 10 marking options (depicting 8 aircraft) for 1st, 514th, 518th, 520th, 522nd (83rd SOG), 524th, and 530th Fighter Squadrons. The markings provided span from the early 1960s to the early 1970s, and also includes two Sk...
Aircraft decals (military)
Catalogue
£18.50
-
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

AOA Decals AOA48012
VIETNAMESE SPADS (2) VNAF Douglas AD-6/A-1H Skyraiders in the Vietnam War For Tamiya 1/48 Douglas A-1H/A-J Skyraider Part 2 of the South Vietnam Air Force (VNAF) Skyraider coverage, this 1/48 decal sheet includes 11 marking options (depicting 10 aircraft) for 1st, 514th, 516th, and 518th Fighter Squadrons. The markings provided span from the early 1960s to 1970, and also includes two Skyraiders that were retrofitted with...
Aircraft decals (military)
Catalogue
£18.50
-
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

AOA Decals AOA48004
1:48
Douglas Skyhawk Airframe Stencils (Hi-Viz) - A-4A/A-4B/A-4C/A-4E/A-4F/A-4L/A-4M This sheet provides the A-4 Skyhawk airframe stencils for one aircraft in the high-visibility Navy light grey/white camouflage scheme. The Skyhawk's stenciling varied (even from aircraft to aircraft within same squadron and time period) - it would be near impossible to capture every style and variant. This sheet captures one of the most typical ov...
Aircraft decals (military)
Catalogue
£11.50
-
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

AOA Decals AOA48001
1:48
GRUMMAN INTRUDERS FROM THE BEACH USMC A-6A Intruders in the Vietnam War This extensive and comprehensive decal sheet covers the three land based USMC A-6 Intruder squadrons during the Vietnam War (VMA(AW)-225, -242, and -533); allowing the option to represent one of many possible Intruders from any timeframe for each squadron. 64 Marking Options (45 Aircraft) Included: 7 options (5 aircraft) for VMA(AW)-225 Vikings wh...
Aircraft decals (military)
Catalogue
£17.50
-
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