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 = 956 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 16 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 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 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 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 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 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

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 ILD72014
1:72
Early Curtiss P-40s & Tomahawks. Mk.IIB 73 Sqn Egypt 1941; 33rd Squadron, 1940; Mk.IIA 403Sqn RCAF, Bennington, May 1941; Mk.IIB, AK431, Western Desert late 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 ILD72016
1:72
Berlin Airlift Douglas C-47A 'Fassberg Flyer' 'European Air Transport Service' BOAC Speedbird G-AGIZ
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 ILD72013
1:72
Pre-War Hawker Hurricanes Hurricane Mk.I, L1934, of No. 3 Sqn., July 1939. Hurricane Mk.I, L1568, of No. 73 Squadron, 1938. Hurricane Mk.I, L1643, of No. 85 Sqn., winter 1938/39 Hurricane Mk.I, L1975, of No. 17 Squadron, early in 1939. Hurricane Mk.I, L1990, of No. 56 Squadron, mid 1939. Hurricane Mk.I, 315, of No. 1 Squadron RCAF, Summer 1939. Hurricane Mk.I, L1606, re-registered G-AFKX, used as a demonstrator and test...
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 ILD72011
1:72
North-American P-51D Mustang ANG/Air National Guard. P-51D, 154th FS, Arkansas National Guard. P-51D, 44-73205, of the 110th FS, Missouri National Guard, late 1946. P-51D, 44-73564, 175th FS, 132nd FG, South Dakota National Guard P-51D, 45-11349, 113th FS, Indiana National Guard, 1949. P-51D, 44-63746, 188th FS, New Mexico National Guard, 1947.
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 ILD72009
1:72
Pre-War Supermarine Spitfire Mk.I Spitfire Mk.I, K9795, of 19 Sqn., Duxford, October 1938. Spitfire Mk.I, K9843, of 54 Sqn., Hornchurch, summer 1939. Spitfire Mk.I, L1088 , of 609 Sqn. Spitfire Mk.I, K9927, of 14 Sqn., Hornchurch, May 1938. Spitfire Mk.I, K9906 flown by Flying Officer R. Stanford Tuck 72 Sqn., April 1939. Spitfire Mk.I of 72 Sqn., Hornchurch, 1939. Spitfire Mk.I, of 19 Sqn., Duxford, May 1939.
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 ILD72008
1:72
Lockheed P-38J/P-38L Lightning (8) Black 7 80th FS/8th FG Lt Ken Ladd New Guinea; 79th FS/20th FG Col.H.Rau 'Gentle Annie' with D-Day stripes; 7th FG 'Miss Ann' experimental night intruder scheme; LC-E 7thFS/ 20thFG James Morris 'My Dad' RAF Kingscliffe 1944; 42-7121 7th FS Maj.J.A. Watkins 'Charlcie Jean' New Guinea 1944; 44-25786.
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 ILD72006
1:72
Strange Hurricanes Hurricane Mk.IIb, BN125 of 681 San., India, Feb 1943. Hurricane Mk.I, P2992, of 527 Radar Calibration San., Hornchurch, 1943. Hurricane Mk.IV, KZ225 of 1 (Indian)Service Flying and Training School, Ambala, 1946. Hurricane Mk.I, L2048, sent to Poland July 1939. Hurricane Mk.IIb (trop), captured and test-flown by the Japanese. Hurricane Mk.IIb of the Turkish Air Force, September 1943.
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

Iliad Design ILD72005
1:72
Martin B-26B Marauder (5) 296185 4T-S 56th BS Holland 1945; 295867 IH-A1 1st Pathfinder Sqn with shark mouth; 295894 K9-P 494th BS/344th BG 'Georgias on my Mind'; 243279 Number 44 442nd BS320th BG 'The Old Warrior'; 2107783 Number 23 441st BS/320th BG 'Thumper'.
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 ILD72002
1:72
Oddball Supermarine Spitfire Spitfire Mk.I, AR213, of 57 OTU, early 1942. Spitfire Mk.IXe of Capitaine Gabriel Gauthier, Commandant of GC II/7. Spitfire Mk.VB of 41 Squadron, Hawkinge, Kent, June 1942. Spitfire Mk.Vc, JK764, of 352 (Yugoslav) Sqn., Vis airfield (Croatia), 1944. Spitfire Mk.Vc, AA963 in US camouflage and markings, Feb. 1942. Mk.I/Mk.VC/Mk.IXE/
Aircraft decals (military)
Catalogue
£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