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=362108 AND ps.id = 953 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 52 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
Print Scale PSR32012
1:32
AWG-10 APG-59 Radar for McDonnell Phantom II F-4J/F-4S Aircraft equipped with this radar F-4J Phantom II F-4J(UK) Phantom II F-4S Phantom II McDonnell-DouglasFG.1/ FGR.2 Phantom
Aircraft detailing sets (resin)
Catalogue
£29.99
£26.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

No Image
Print Scale PSL32035
Messerschmitt Bf-109G-6 Part 2 1. Me 109G-6 of 2/JG 300. Red 6 Germany summer 1944. 2. Me 109G-6 of 5./JG 53 aâé-l"Pikasaâé- Vienna (Austria), January 1944. Pilot: Oberfeldwebel Herbert Rollwage. 3. Me 109G-6 of I/JG 300. Helsinki-Malmi (Finland), March 1944. 4. Messerschmitt Bf-109G-6 of 2/JG 301 Germany, winter 1944. 5. Me 109G-6 of Stab I./ JG 27. Fels-am-Wagram (Austria), May 1944. Pilot: Hauptmann (captain) Ludwi...
Aircraft decals (military)
Catalogue
£22.80
£20.52
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

No Image
Print Scale PSL32037
1:32
Luftwaffe Insignia. Winkel
Aircraft decals (military)
Catalogue
£25.99
£23.39
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

No Image
Print Scale PSL32030
Messerschmitt Bf-109G-6 Part 1 Messerschmitt Bf-109 G-6 Part 1 / 32-030 / 1. Messerschmitt Bf-109G-6 (Red 4) of Oberfeldwebel Rudolf Trenkel 2/JG 52 . Poltava, July 1943. 2. Messerschmitt Bf-109G-6 of IV./JG 5 "Eismeer" Petsamo (Finland), December 1943. 3. Messerschmitt Bf-109G-6 of Stab/JG 52. Russian front, winter 1943-1944. Pilot: Oberleutnant Dietrich Hrabak. 4. Messerschmitt Bf-109G-6 6/JG 52 Bagerovo (Russian ...
Aircraft decals (military)
Catalogue
£21.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

No Image
Print Scale PSL32036
Luftwaffe Insignia 1939-1945
Aircraft decals (military)
Catalogue
£19.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
Print Scale PSL047
1:144, 1:200, 1:24, 1:32, 1:35, 1:48
RAF Dark Earth strips
Aircraft decals (military)
Catalogue
£8.30
-
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
Print Scale PSL048
1:144, 1:200, 1:24, 1:32, 1:35
RAF Dark Green strips
Aircraft decals (military)
Catalogue
£8.30
-
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
Print Scale PSL049
1:144, 1:200, 1:24, 1:32, 1:35, 1:48
RAF Medium Sea Gray strips
Aircraft decals (military)
Catalogue
£8.30
-
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
Print Scale PSL3D32-001
Messerschmitt Bf-109G-6 (Early) (designed be used with Trumpeter kits)
3D printed decal
Catalogue
£6.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

Print Scale PSL32028
1:32
Northrop P-61B Black Widow The complete set 2 sheets. P-61B-15 42-39606 'Ll L ABNER' , crewed by Lt Alvin G Moore and R/O Lt Juan D Lujan, 415th NFS, St Dizier, France, March 1945. P-61A-10 42-5623 , Sweatin Wally, flown by Capt Walter A Storck, 427th NFS, Myitkyina, Burma, late 1944. P-61A-10 42-5565 'DOUBLE TROUBLE' , crewed by 2lt Robert G Bolinder and R/O Flt Off Robert F Graham, 422nd NFS, Etain ,France late 1944. ...
Aircraft decals (military)
Catalogue
£21.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

No Image
Print Scale PSL32034
Vought F4U Corsair in Korean War Part 2 1. F4U-4 Bu.No. 97380 WR5 flown by Capt Phillip C. DeLong of VMF-312, USS Bataan, April 1951. 2. AU-1 Bu.No. 192359 LD10 'MISS PENNY' assigned to Capt Dick Fransisco of VMA-212 Named for the daughter of Capt Forest Nelson, Penny Nelson.
Aircraft decals (military)
Catalogue
£19.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

No Image
Print Scale PSL32031
Bell UH-1 Air Ambulance in Vietnam War
Aircraft decals (military)
Catalogue
£23.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
Print Scale PSL32032
Bell UH-1 Air Ambulance in Vietnam War. Technical Stencils
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

No Image
Print Scale PSL32025
1:32
Polikarpov I-153 Part 1 1. I-153 Unit: 1st AE, 71st IAP, 61st AB, VVS KBF. Serial: 50. Pilot - Deputy commander of 1st Sqn Maj.Lt.Alexsandr Gerasimovich Baturin, won 9 wins+13, Bychye Pole, Soviet Union, Summer 1942. 2. I-153. Unit: 123rd IAP, 10th SAD. Serial: 2. This aircraft was captured by German troops at Bobruisk airfield in July 1941. 3. I-153. Unit: 3rd AE, 32nd IAP, VVS of the Black Sea Fleet. Serial: 3/3. Seva...
Aircraft decals (military)
Catalogue
£19.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

Print Scale PSL32023
1:32
Vought F4U-4 Corsair The complete set. 1. F4U-4 Bu.No. 81754 B216 flown by LtJg Howard W. Westervelt, Jr., of VF-192, USS Princeton, September 1952. 2. F4U-4 Bu.No. 81712 A211 'DOT' of VF-791.
Aircraft decals (military)
Catalogue
£20.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

Print Scale PSL32029
North-American F-51 Mustang Units of the Korean War (P-51D) F-51D-30 45-11613 of the 45th TRS/67th TRW, Kimpo AB (K-14), September 1951. F-51D-30 44-74283 of the 80th FIS/8th FBG, Seoul City AB (K-16), December 1950. F-51D-30 45-11412 of the 12th FBS/18th FBG, Chinhae AB (K-10), March 1951.
Aircraft decals (military)
Catalogue
£21.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

Print Scale PSL32026
1:32
Polikarpov I-153 "Chaika" Part 2 1. I-153 Unit: JVS 3. Serial: 101. Vienna-Schwechat, 1942. The plane was used as an advanced fighter trainer. 2. I-153 Unit: Stab./Luftlandegeschwader 1. Serial: H4+MB. 3. I-153 Unit: 3/LeLv 6Serial: 6. Pilot - Kapteeni Per-Erik Ahonius, CO of 3/LeLv 6. Rompotti, USSR (now Russia), July 1942. 4. I-153 VH-12 was captured and flown by the FAF. The aircraft was lost when it crashed, due...
Aircraft decals (military)
Catalogue
£19.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

Print Scale PSL32027
1:32
Civil War in Spain. Franco
Aircraft decals (military)
Catalogue
£10.60
-
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

Print Scale PSL32024
1:32
Messerschmitt Me-163B 1. Me-163B-0 (V-2, Messerschmitt built) Unit/Location: Erprobungskommando 16, Peenemƒ¼nde-West/Germany, 24.06.1943. Code: VD-EL. 2. Me-163B-0 (V-21, Messerschmitt built) Unit/Location: Erprobungskommando 16, Peenemƒ¼nde-West/Germany, June1943. Code: VA+SS. 3. Me-163B-0 (V-35, Messerschmitt built) Unit/Location: Erprobungskommando 16, Bad Zwischenahn, Spring 1944.Code:GH+IN. 4. Me-163B-0 (V-4...
Aircraft decals (military)
Catalogue
£21.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

Print Scale PSL32021
McDonnell F-15E Eagle 1. 88-1667, 391st Fighter Squadron 'Bold Tigers' 2. 'I'll Take It From Here' 89-0506, 391st Fighter Squadron 'Bold Tigers'
Aircraft decals (military)
Catalogue
£18.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

Print Scale PSL041
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 65v.1 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL042
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 70 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL043
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 71 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL044
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 74v.1 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL045
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 79 stripes
Aircraft decals (military)
Catalogue
£7.99
-
In Stock
3

   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