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 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 65 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 ILD72021
Even more colourful DHC-6 Twin Otter markings. There are aircraft of the United Nations, US Army Parachute Team in D-Dan markings, Panamanian Navy, Ethiopian Army, a special Chilean SAR scheme, and a Peruvian aircraft on floats.
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

No Image
Iliad Design ILD72015R
de_Havilland_Canada DHC-6 Twin Otter. Contains markings for the Ecuadorian Air force; Canada's Dept. of Transport surveillance aircraft; US Army Alaska National Guard; Guatemalan Air Force; Committee of the Red Cross; and French Air Force.
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

No Image
Iliad Design ILD72020
Unusual Douglas C-47s. RCAF Dakota Mk.III in United Nations livery; C-47B operated under contract by Carco Air Service; C-53 flown by Northeast Airlives pilots; and a C-47 used by the Military Attachĩ in Wellington, New Zealand
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 ILD72012
1:72
Re-printed! Naval T-Birds Lockheed T-33A Shooting Stars Lockheed TV-2 in natural metal finish, attached to NAS Glenview, Illinois. A TV-2 drone controller of GMGRU-1 (Guided Missile Group 1) mid 1959. US NAVY T-33B of the Naval Air Training Command, stationed at NAS Glynco, ca. 1974. US MARINES TV-2 based at MCAS El Toro, Oct. 1961. TV-2 of Marine Air Squadron 32.
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 ILD72018
1:72
Air National Guard Douglas C-47A Skytrain. Puerto Rico Air Guard 0-92079; 'The Old Dominion' Virginia Air National Guard 1948; Nebraska National Guard NG315555; A60 New Mexico National Guard circa 1947 NG330691;
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 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 ILD72019
1:72
North-American B-25B, B-25C & B-25D Mitchell B-25B, 3rd Antisubmarine Sqn., Westover, Mass., ca. 1942. B-25C used for advanced training at Columbia, South Carolina, 1942. B-25D, Screaming Meemie, 487th BS, 340th BG, Sicily, late 1943. B-25C FAT CAT, Dobodura, New Guinea, mid-1943. RCAF B-25D Mitchell at 5OTU, Boundary Bay, British Columbia, 1945. [B-25C/D]
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 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 ILD72017
1:72
Stars in the Sky 3 Avro Lancaster @Lady Be Good' Robert Clothier, Vought F4U-1 Corsair Jerry Coleman, McDonnell F-4 Phantom Dean Paul Martin, Californis Air National Guard; Boeing B-29 Superfortress 'City Of Monroe' Tim Holt
Aircraft decals (military)
Catalogue
£13.99
-
In Stock
2

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