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=7992 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 341 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

Eagle Cal EAG72114
1:72
Supermarine Spitfire Mk.IX (3) MA585 KH-B 403 Sqn RCAF P/O Buzz Beurling; EN354 WD-W 52nd FG Lt Leonard V. Helton N.Africa camouflage; MH454 FU-N 453 Sqn RAAF F/O J.Boulton Full D-Day stripes
Aircraft decals (military)
Catalogue
£10.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

Eagle Cal EAG48040
1:48
Messerschmitt Bf-109G-6 (4) White <<< JG 54 Oberst Hannes Trautloft 1943; Yellow 9 9./JG 54 Blue fuselage band Fw Emil Hecker 1944; Black 14 8./JG 1 1944 Red band; Black << III./JGII Yellow band Hptm Anton Hackl
Aircraft decals (military)
Catalogue
£14.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

Eagle Cal EAG72108
1:72
Focke-Wulf Fw-190D-9 Part 2 (4) White 17 1/JG 2 RLM 75/76/83; Yellow 9 3/JG 2 RLM 75,76,83; Black
Aircraft decals (military)
Catalogue
£10.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

Eagle Cal EAG72109
1:72
Focke-Wulf Fw-190D-9 Part 2 (4) White 17 1/JG 2 RLM 75/76/83; Yellow 9 3/JG 2 RLM 75,76,83; Black
Aircraft decals (military)
Catalogue
£10.99
-
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

Eagle Cal EAG72110
1:72
Focke-Wulf Fw-190D-9 Part 3 (4) Blue 4+- and Blue 11+- 8/JG 6 RLM 76/76/83; < I Stab JG 4 Black/white fuselage band RLM 74/75/76; <4 JG 6 RLM 74/75/76
Aircraft decals (military)
Catalogue
£10.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

Eagle Cal EAG32108
1:32
Focke-Wulf Fw-190D-9 Pt 1 (4) White 5 III/JG 2 RLM 74/75/76;
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32109
1:32
Focke-Wulf Fw-190D-9 Part 2 (4) White 17 1/JG 2 RLM 75/76/83; Yellow 9 3/JG 2 RLM 75,76,83; Black
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32110
1:32
Focke-Wulf Fw-190D-9 Part 3 (4) Blue 4+- and Blue 11+- 8/JG 6 RLM 76/76/83; < I Stab JG 4 Black/white fuselage band RLM 74/75/76; <4 JG 6 RLM 74/75/76 EAG72111 Focke-Wulf Fw-190D-9 (Pt 4) Black 3 II/JG 6 RLM 75/76/8
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32062
1:32
Messerschmitt Bf-109K-4 (4) Red 1 10./JG 27 RLM 76/81/83 green defence band; Blue 16 12./JG 27 Berlin RLM 76/75/83 green defence band; Red 7 10./JG 27 Prague RLM 76/81/83 green defence band; Green 4 Stb JG 52 Hermann Graf RLM 76/75/83 red scalloped nose. All 1945.
Aircraft decals (military)
Catalogue
£17.50
-
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

Eagle Cal EAG48060
1:48
Focke-Wulf Fw-190D-9 (4) Black 1 14/JG 26 RLM 76/82/83; Black 11 JG 4 RLM 75/76/83; Blue 12 II/JG 6 RLM 76/82/83; White 11 13/JG 51 RLM 76/82/83;
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

Eagle Cal EAG32082
1:32
Focke-Wulf Fw-190A-5 (4) Black 1 II/JG 1 Hptm Dietrich Wickop; Yellow 2 9/JG 2 Oblt Josef Wurmheller; << I/JG Hptm Walter Nowotny; White 7 1/JG 1 Uffz Walter Kohne. All in variations of RLM 74/75/76
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG72111
1:72
Focke-Wulf Fw-190D-9 (Pt 4) Black 3 II/JG 6 RLM 75/76/83 Black 5 Stab JG 26 RLM 74/75/83; Black 14 III/JG 2; Black 10 2JG RLM 74/75/83.
Aircraft decals (military)
Catalogue
£10.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

Eagle Cal EAG32038
1:32
Messerschmitt Bf-109G-5/Bf-109G-6 (4) Yellow 1 Schilling 9.JG 54; Yellow 11 Gerhard Kroll 9.JG 54; White 1 Klemm 7.JG 54; all have blue tail band and yellow rudder and under nose; White 7 Kuhlein II.JG 51
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32043
1:32
Messerschmitt Bf-109G-14 (4) Yellow 10 3/JG 77 RLM 74/75/76 Green/white defence bands; Blue 3 4/JG 77 RLM 74/75/76; Black 8 10/JG 4 RLM 74/75/76 black/white defence bands; White 21 II/JG 52 RLM 74/75/76 yellow rudder.
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32050
1:32
Major Hans 'Assi' Hahn Part 1 Messerschmitt Bf-109E (4) E-3 StabI./JG 3 Merseburg Germany 1939; E-3 White 13 4./JG 2 Northern Germany Spring 1940; E-4 White 14 4./JG 2 France 1940; E-4 III./JG 2 France 1940
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32051
1:32
Major Hans 'Assi' Hahn Part II Messerschmitt Bf-109F (4) F-2 Wr 5749 III./JG 2 Mid 1941 (2 schemes); F-4 Wr 7183 III..JG 2 France Sept 1941; F-4 Oblt. Puchlinger III./JG 2 Summer 1941; All St Pol France
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32057
1:32
Focke-Wulf Fw-190D-9 (4) Yellow 15 7/JG 301 red/yellow fuselage band; Blue 9 II/JG 6 RLM 81/83/76; Black <-+- 'Christl' Stab/JG 6 Gerhard Barkhorn; Black
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32058
1:32
Focke-Wulf Fw-190D-9 (4) Brown 18 7/JG 26 Lt Seyd black/white fuselage band; Black <-+- JG 4; Black <1+~ Stab IV/JG 51 currently on display USAF Museum Dayton OH; Black 6 III/JG 2 yellow/white/yellow fuselage bands
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32059
1:32
Focke-Wulf Fw-190D-13/R11/D-11/D-9 (4) Yellow 10 Stab JG 26 Now on display at Mesa,AZ,USA; Black 14 Stab JG 26; White 61>; White <<- . Both Verbandsfuhreschule Gdj 1945; All variations of RLM 75/76/82/83 camouflage schemes
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32060
1:32
Focke-Wulf Fw-190D-9 (4) Black 1 14/JG 26 RLM 76/82/83; Black 11 JG 4 RLM 75/76/83; Blue 12 II/JG 6 RLM 76/82/83; White 11 13/JG 51 RLM 76/82/83;
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32063
1:32
Messerschmitt Bf-109K-4 (4) White 6 9./JG 27 green defence band; Black 15 IV./JG 53 black defence band; Yellow 13 15./JG 53 black defence band; Black 5800 yellow fuslage band with red outline. all variations on RLM 76/75/83
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32065
1:32
Messerschmitt Bf-109F-4/Bf-109G-2 Graf and Grislawski 9/JG 52 Part 2 (4) Bf-109G-2 Graf Yellow 11+~; Grislawski Yellow 9+~; F-4 Yellow 9+~; Variations of RLM 74/75/76; Yellow 8+~.RLM 70/74/75/76. All yellow nose and fuselage band
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32067
1:32
Messerschmitt Bf-109G Graf and Grislawski 9/JG 52 Part 4 (4) Grislawski Bf-109G-2 Yellow 10+~; Bf-109G-4 White 9 (ex NI+GU) both Russia 1942/43; Graf Bf-109G-6 <<+- Stab/JG 52 Poland 1944. All in variations of RLM 74/75/76. G-5 <<+- Stab/JG 11 overall RLM 76 hi-altitude scheme. All have yellow RLM 04 lower nose and tail band
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32073
1:32
Curtiss P-40B Tomahawk IIB (2) AK461/A 112 Sqn North Africa camouflage schemes shark mouth; AK490 79 Sqn S/L Peter Wykeham-Barnes Dk Green/Dk Earth/Lt Grey
Aircraft decals (military)
Catalogue
£17.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

Eagle Cal EAG32075
1:32
Messerschmitt Bf-109K-4 (4) Black < Stab III/JG 27 with RLM 25 green tail band; Yellow 1 II/JG 53 Lt G.Landi; Black 8 14/JG 53; White 10 9/JG 27. All variations of RLM 75/76/83
Aircraft decals (military)
Catalogue
£17.50
-
In Stock
3

[1]   «      9   |   10   |   11   |   12   |   13      »      [14]
-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