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=8062 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 34 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
Iliad Design ILD48042
1:48
BCATP Avro Ansons
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
Iliad Design ILD48043
Lockheed P-38J Lightnings including nose art for Jewboy, Miss Ann, My Dad, Gentle Annie
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
Iliad Design ILD48041
Piper L-4 Grasshopper Colourful and unusual L-4s. There are US Army, US Navy, and Free French aircraft, including one that shot down a German aircraft in a dogfight!
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
Iliad Design ILD48002R
Re-printed! Messerschmitt Bf-109G-6/R6 Tropical version (6) 7./JG 53 'Cartoon Aircraft'. White 2, 3, 5, 6, 8 and 9 all with different cartoon artwork Sicily June 1943. All have similar camouflage of RLM 74/75/76 with yellow undernose, white tail bands and black/white spiral spinners
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
Iliad Design ILD48019R
Re-printed! Special Ops Douglas A-1H Skyraiders (4) 39665/TC 1st SOS Lt Randy Scott Thailand; 37628/TS 22nd SOS Shark Mouth Thailand; 37552/6T 6th SOS Col.Sam Berman Da Nang 1969 with red/white/blue fin and rudder; 34609/TS 22nd SOS `Bad News'; All in standard SEA camouflage; 39797/UL 83rd SOG Vietnam Air Force Tan.Dk Brown camo
Aircraft decals (military)
Catalogue
£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

No Image
Iliad Design ILD48039
Northrop T-38C Heritage Schemes
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
Iliad Design ILD48040
More Naval Lockheed T-33 T-Birds
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
Iliad Design ILD48037
Convair F-106B sheet contains a selection of colourful aircraft in operational and special schemes.
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
Iliad Design ILD48038
ANG North-American P-51 Mustangs part 3 sheet we have outdone ourselves with a group of very unusual and colourful aircraft.
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

Iliad Design ILD48020
Lockheed F-104 Starfighters in Combat (6) Lockheed F-104A 'D 'Pakistan Air Force; Lockheed F-104C 0-70927 'Debbbie Sue 'and 0-60910 'The Pussycar '8th TFW Udorn; Lockheed F-104G FG-770/4344 427th TFS/3rd TFW Republic of China Air Force; 22313 141 Filo Turkish Air Force;
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
Iliad Design ILD48036
More ANG/Air National Guard North-American P-51D Mustangs. Washington, Texas, Minnesota and Oklahoma.
Aircraft decals (military)
Catalogue
£13.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

Iliad Design ILD48034
Mikoyan MiG-21F-13s The first Shenyang J-7 (Chinese-built version of the MiG-21F-13), of the PLAAF, in its roll-out markings. Iraqi MiG-21F-13. Avia S-106 (Czech-built MiG-21F-13) of the Syrian Air Force. Albanian Air Force MiG in post-Communist markings. Czech Air Force Aero S-106 in special scheme for aviation day. North Korean MiG-21F-13.
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

Iliad Design ILD48035
1:48
Cessna L-19/0-1 Birdog O-1A Bird Dog of the Kansas Army National Guard, 1968 L-19 of the Pakistan Army's 11th Army Aviation Squadron (Rachna Eagles). L-19, one of four, used by the South Korean navy in the early 1960s. O-1A of the US Army Reserve. L-19 of the 203rd RAC (Recon Airplane Company), L-19 operated by the Hoantai, the predecessor of Japan's Self Defence Force,
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

Iliad Design ILD48005
Mikoyan MiG-19/Shenyang J-6 (5) Mikoyan MiG-19 3570 Egyptian Air Force 1967 Natural metal; J-6 No 4126 26 Sqn Pakistan Air Force FS36118/36270/36375; No 3878 Egyptian Air Force FS36375/35550; No 10434 Pakistan Air Force Retirement scheme in red, white, green fuselage; 6066 North Vietnamese Air Force 925th Fighter Regt 1972 Dark green over natural metal
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

Iliad Design ILD48032
1:48
Schlacht Messerschmitt Bf-109E-4/Bf-109E-7 Bf-109E-4B flown by Georg Dorfell of II./SG1, Bf-109E-7 Trop of 7./ZG1, near El Alamein, Aug. 1942. Bf-109E-4B of II.(Sch)/LG2, Russia, Summer 1941. Bf-109E-4B of II./LG2, Russia, 1941. Bf-109E-4B of 6.(Sch)/LG2, France, 1940. Bf-109E-4B of 5.(Schl)/LG2, Russia, 1941.
Aircraft decals (military)
Catalogue
£13.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

Iliad Design ILD48033
1:48
Beechcraft 18 SNB-2H hospital aircraft stationed at NAS Jacksonville. UC-45J assigned to NAS Atsugi, Japan, mid 1960s. Beech AT-7C of an unknown unit, ca. 1946. [UC45 JRB-4 C-45F/C-45F]
Aircraft decals (military)
Catalogue
£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

Iliad Design ILD48003
1:48
Pre-War Supermarine Spitfire Mk.I/Mk.1 (5) K9795 19 Sqn Duxford 1938 2 blade prop; K9843 DL-N 54 Sqn Hornchurch 1939 2 blade prop; L1088 PR-E 609 Sqn 3 blade prop; K9927 JH-B 14 Sqn Hornchurch 1938 3 blade prop; 19 Sqn WZ-C Duxford 1939 2 blade prop.
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

Iliad Design ILD48031
1:48
Beechcraft 17 Staggerwing Beechcraft D17S flown by Prince Bernhardt of the Netherlands Beechcraft UC-43 of an unknown unit with an unusual white tail UC-43 at Wright Field, July 1944. Beechcraft D17A used in the Antarctic Service Expedition in 1940. Beechcraft D17R of the Chinese Air Force in overall white. Beechcraft D17A of the Chinese Nationalist Air Force now camouflaged [G17S UC-43 D17S GB-2]
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

Iliad Design ILD48001
1:48
Hawker Hurricane Mk.I (4) Pre-war aircraft. L1568/S 73 Sqn 1938; L1975 UV-K 17Sqn 1939; 315 1 Sqn RCAF 1939 all with fabric covered wings/2 blade Watts props. L1990 LR-R 56Sqn 1939. All Dk Earth/Dk Green/Aluminium. 17 and 56Sqn have black port wing undersurface
Aircraft decals (military)
Catalogue
£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

Iliad Design ILD48024
1:48
Air National Guard North-American P-51D Mustangs Arkansas, Missouri, South Dakota and Indiana ANG
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

Iliad Design ILD48030
1:48
Stearman PT-17 Kaydet PT-17 used by 4BFTS (British Flight Training School) at Mesa, Arizona, early 1940s. Camouflaged PT-17, 216546, Kunming, China, 1943. N2S-3 (US Navy designation for PT-17), 39123, ca. 1944. N2S-4, 29950, Grosse Ile, Michigan, Oct., 1943. PT-27 (RCAF version of the PT-17), FJ888, of 36 EFTS, Pearce, Alberta, Summer 1942.
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

Iliad Design ILD48029
1:48
Republic P-47D Thunderbolts Hawaii, Puerto Rico and Maryland ANG
Aircraft decals (military)
Catalogue
£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

Iliad Design ILD48028
1:48
Supermarine Spitfires over India. Mk.VIII 607 Sqn, Imphal, India May 1944; Mk.VIII Wg Cdr Frank Carye, AFTU, Amarda Road, India 1944; Mk.VIIIC No 3 Indian Group Communications Flight 1946; Mk.XIVe SFTS, RIAF Ambala, India; Mk.XIVe NH861, 8 Sqn Royal Indoan Air Force 1947; Mk.XVIII AFS (India)
Aircraft decals (military)
Catalogue
£13.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

Iliad Design ILD48023
1:48
Canadian-built de Havilland Mosquitos (6) de Havilland Mosquito B.XX KB162 XD-J 'New Glasgow' 139 Pathfinder Sqn 1944 2 versions; B.VII KB315/334926 'The Spook' USAF; B.XX KB196 AZ-B 527 Sqn with D-Day stripes; FB.26 KA406 1000th Canadian built; FB.26 Chinese Civil War.
Aircraft decals (military)
Catalogue
£13.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

Iliad Design ILD48022
1:48
Milestone McDonnell Phantom (6) F-4B 152276 1000th 7/7/65; F-4D 66-7533 2000th 12/3/67; F-4J 155772 3000th 5/9/68; F-4E 69-7294 4000th 1/2/71; F-4E 70290 5000th 24/5/78; F-4E 80744 5057th The last F-4 U.S. built.
Aircraft decals (military)
Catalogue
£13.99
-
In Stock
3

   1   |   2      »      [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