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=8232 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 380 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

Techmod TM48001
1:48
Supermarine Spitfire Mk.I/Supermarine Spitfire Mk.IIb (3) RF-A 303 Sqn 1941; R6908 qJ-F 92 'East India' Sqn1940; P8342 UZ-N 306 Sqn 1941
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM48077
1:48
Vought SB2U Vindicator/Chesapeake (3) V-156-F AB1-6 and AB1-12 Both Escadrille AB1 1939-40 overall matt blue/grey; V-156-B1 AL924 RAF 911 Sqn Lee-on -Solent 1941 dark earth/dark green/sky [SB2U-1 SB2U-2 SB2U-3]
Aircraft decals (military)
Catalogue
£7.20
-
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

Techmod TM72118
1:72
US Serials and Code Numbers Yellow. 9 sizes 6" ,8" ,12" ,16" ,20" ,24,28,32 and 36"
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM72136
1:72
Dornier Do-17Z (6) A1+BZ 15 (Croatian)KG53 Russia 1942; F1+FS 8/KG76 France 1940; 5K-BN 5/KG3 East Prussia 1938; DN-53 3/LeLv46; DN-63 1/LeLv 46; Both Finnish Air Force 1942; DN-52 PleLv43 Finland 1947;
Aircraft decals (military)
Catalogue
£5.40
-
In Stock
1

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

Techmod TM72140
1:72
PZL P.7 part 1 Camouflage & markings versions PZL P.7a, 121 Squadron, 2nd Aviation Regiment, Polish Air Force PZL P.7a, 111 Squadron, 1st Aviation Regiment, Polish Air Force PZL P.7a, 151 Squadron, 5th Aviation Regiment PZL P.7a, 162 Squadron, 6th Aviation Regiment, Polish Air Force PZL P.7a, 123 Squadron, 2nd Aviation Regiment, Polish Air Force PZL P.7a, Polish Air Force Aviation School, Dęblin PZL P.7a, 132 Squad...
Aircraft decals (military)
Catalogue
£5.40
-
In Stock
1

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

Techmod TM48007
1:48
Poland National Insignia.15 sizes
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM48020
1:48
Soviet Stars. With white and red/white outlines. 6 sizes Soviet Stars. With white and red/white outlines. 6 sizes
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM48808
1:48
Douglas TBD-1 'Devastator' at War (7) White T1 VT-2 USS Lexington; Black 8 VT-6 USS Enterprise; 7-T-7 VT-7 USS Wasp; Black T-18 VT-8 USS Hornet; Black 1 VT-5 USS Yorktown; Black 1 VT-6 USS Enterprise.All FS35189/FS36440. 71-S-16 VS-71 USS Wasp yellow wings, black tail. Inc paint masks
Aircraft decals (military)
Catalogue
£11.20
-
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

Techmod TM72001
1:72
Messerschmitt Bf-110C (3) M8+GK Hptm Wolfgang Falck Staffelkapitan 2/ZG76 1939 RLM 70/71/65; U8+BB Hptm Gunther Sprecht I/ZG26 France 1940 RLM 02/71/65; LN+1R Theodor Weissenberger Norway 1941 RLM 74/75/76
Aircraft decals (military)
Catalogue
£4.50
-
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

Techmod TM72007
1:72
Messerschmitt Bf-109F-2 (4) JG 26 CO Adolf Galland; II/JG 54 CO Hptm Hrabak; 1/JG 3 CO Hptm Hans von Hahn; Black 7 II/JG 3 Lt.Meckel
Aircraft decals (military)
Catalogue
£4.50
-
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

Techmod TM72051
1:72
Messerschmitt Bf-109G-2 (4) Yellow 11 9/JG 52 Oblt Herman Graf 3/1943; Yellow 3+- 6/JG 5 Focke-Wulf Fw-Rudolf Muller; Yellow 12 6/JG 5 Oblt Heinrich Ehrler; Both Finland 3/1943; Black 1 15 GJ/52 Franjo Dzel.yellow nose Inc canopy mask
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM72078
Messerschmitt Bf-109E-4 (5) Wr 5819 JG 26 Adolph Galland; Stab.III/JG 1 Reinhard Heydrich; 9./JG 26 Yellow 1 Gerhardt Schopfel; I./JG 3 Hans Von Hahn; Kommandeur III./JG 2 Erich Mix; All France 1940-41
Aircraft decals (military)
Catalogue
£4.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

Techmod TM72104
1:72
Fokker D.VII in Polish service 1920/21 (5) 15 FS; Lawica Air Station; CO 3rd Air Group; Variety of lozenge camouflage schemes
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM72132
1:72
Douglas TBD-1 'Devastator' at War (7) White T1 VT-2 USS Lexington; Black 8 VT-6 USS Enterprise; 7-T-7 VT-7 USS Wasp; Black T-18 VT-8 USS Hornet; Black 1 VT-5 USS Yorktown; Black 1 VT-6 USS Enterprise.All FS35189/FS36440. 71-S-16 VS-71 USS Wasp yellow wings, black tail. Inc paint masks
Aircraft decals (military)
Catalogue
£7.20
-
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

Techmod TM24011
1:24
Messerschmitt Bf-109G-2 (3) Yellow 11 9/JG 52 Oblt Herman Graf 3/1943; Yellow 3+- 6/JG 5 Rudolf Muller; Yellow 12 6/JG 5 Oblt Heinrich Ehrler; Both Finland 3/1943; Inc canopy mask
Aircraft decals (military)
Catalogue
£11.20
-
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

Techmod TM32007
1:32
Grumman F6F-5 Hellcat (4) White 25 VF-19 Bruce Williams USS Lexington 3 tone camouflage; Minzi III Cdr David McCampbell CAG 15 USS Essex; White 9 VF-11 Lt Charles Stimpson USS Hornet; No 71 Lt Leo McCuddin VF-20 USS Enterprise
Aircraft decals (military)
Catalogue
£9.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

Techmod TM32015
1:32
Royal Navy/RN Grumman Hellcat Mk.I FAA (3) JV131 E-L 800 NAS H.M.S. Emperor D-Day stripes; FN430 6-R 1844 NAS H.M.S. Indomitable 1944; JV144 120/W 1839 NAS H.M.S. Indomitable 1945.
Aircraft decals (military)
Catalogue
£9.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

Techmod TM48025
1:48
Hawker Hurricane Mk.I (3) V6665 RF-J Flt Lt J.A.Kent; V6684 RF-F S/L Witold Urbanowicz. both 303 (Polish) Sqn Northolt 9/1940; V7118 UZ-V 306 (Polish) Sqn
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM48032
Junkers Ju-88A-4 (2) 5K+DC II/KG3 Russia 1941 RLM 70/71/65. L1+EH LG1 Benghazi 1941/2 RLM 79/78 Desert camouflage. Inc stencil data
Aircraft decals (military)
Catalogue
£5.40
-
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

Techmod TM24009
1:24
Messerschmitt Bf-109E-3 (3) White 1 1/JG 2 Oblt Otto Bertram May 1940; < 1/JG 3 Udet Lt Hans Hahn Sept 1939; Yellow 1 6/JG 51 Oblt Josef Priller Belgium Oct 1940. Inc Canopy Mask
Aircraft decals (military)
Catalogue
£11.20
-
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

Techmod TM48013
1:48
Messerschmitt Bf-109E-4 (5) G26 CO Adolf Galland 1940; Stab.III/JG 1 Maj Reinhard Heydrich 1941; Yellow 1 9/JG 26 Oblt Schopfel B of B; 1/JG 3 CO Hptm Hans von Hahn; II/JG 2 CO Maj Erich Mix;
Aircraft decals (military)
Catalogue
£7.20
-
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

Techmod TM48074
1:48
Finnish Air Force Swastika National Insignia. 7 sizes on grey backgrounds 1944-45 plus 2 sheets of Finnish Serial Letters and Numbers in black
Aircraft decals (military)
Catalogue
£9.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

Techmod TM72002
1:72
Grumman F6F-3 Hellcat (5) White 30K and White 21K VF-1 USS Yorktown; White 19 VF-6; White 3X VF-51 USS San Jacinto; The Minsi CVG-15 USS Essex
Aircraft decals (military)
Catalogue
£4.50
-
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

Techmod TM72016
1:72
Supermarine Spitfire Mk.Vb (3) EE781 ZX-A 145 Sqn North Africa; AB968 ZF-H 308 Sqn; W3902 WX-T 302 Sqn
Aircraft decals (military)
Catalogue
£4.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

Techmod TM72020
1:72
Grumman F6F-5 Hellcat (4) White 25 VF-19 Bruce Williams USS Lexington 1944 3 tone camouflage; Minsi III Cdr McCampbell CAG 15 USS Essex; White 9 C.Stimpson VF-11 USS Hornet; Triangle 71 Lt McCuddin VF-20 USS Enterprise. All overall gloss sea blue.
Aircraft decals (military)
Catalogue
£4.50
-
In Stock
4

[1]   «      9   |   10   |   11   |   12   |   13      »      [16]
-10

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