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=8189 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 153 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

RS Models RSMI92039
1:72
Zlin-XII open cockpit 2 Czechoslovakia and Slovakia WWII
Aircraft kits (injection)
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

RS Models RSMI92071
Dornier Do-17E
Aircraft kits (injection)
Catalogue
£32.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

RS Models RSMI92072
Dornier Do-17F
Aircraft kits (injection)
Catalogue
£32.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

RS Models RSMI92126
Kawasaki Ki-100 High back 3 Decal variants for Japan
Aircraft kits (injection)
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

RS Models RSMI92128
Kawasaki Ki-100-II with Turbocharger 3 Decal variants for Japan and Great Britain. Limited edition with universal box !!! With the Ki-100 proving such a success, it was decided to initiate production of this aircraft, the resulting Ki-100-Ib differing only by having the cutdown rear fuselage and all-round-view canopy that had been designed for the proposed Ki-61-III. A total of 99 of this version was built before production...
Aircraft kits (injection)
Catalogue
£19.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

RS Models RSMI92196
Kawasaki Ki-100-II and North-American P-51H Mustang .... two kits in box With the Ki-100 proving such a success, it was decided to initiate production of this aircraft, the resulting Ki-100-Ib differing only by having the cutdown rear fuselage and all-round-view canopy that had been designed for the proposed Ki-61-III. The P-51H was the final production Mustang and used the new V-1650-9 engine, a version of the Merlin that ...
Aircraft kits (injection)
Catalogue
£24.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

No Image
RS Models RSMI92273
Kawasaki Ki-100-I With the Ki-100 proving such a success, it was decided to initiate production of this aircraft, the resulting Ki-100-Ib differing only by having the cutdown rear fuselage and all-round-view canopy that had been designed for the proposed Ki-61-III 4 decal variants: 1. Ki-100, Akeno Flight Training Division (111th Air Regiment, flown by Mj. Yohei Hinoki (12 victories, Miki AF, Hyougo prefecture Japan, July...
Aircraft kits (injection)
Catalogue
£19.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

RS Models RSMI92026
Dornier Do-17P 'Western front' 3 x Luftwaffe
Aircraft kits (injection)
Catalogue
£32.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

RS Models RSMI92154
1:72
Henschel Hs-132B with Jumo 004 engine and extra cannon 5 decal variants 1. Hs-132B, StG 3 /alternate marking/ 2. Hs-132B, KG 66 /alternate marking/ 3. Hs-132B, KG 54 /alternate marking/ 4. Hs-132B, Japenese test machine /alternate marking/ 5. Hs-132B, machine captured by Americans /alternate marking/ Henschel's Hs 132 was a World War II dive bomber and interceptor aircraft of the German Luftwaffe that never saw ser...
Aircraft kits (injection)
Catalogue
£19.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
RS Models RSMI92259
Messerschmitt P.1107 4 decal versions for Luftwaffe
Aircraft kits (injection)
Catalogue
£32.40
-
In Stock
10+

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
RS Models RSMI92255
1:72
Dewoitine D-520 4 decal versions for France
Aircraft kits (injection)
Catalogue
£19.99
-
In Stock
10+

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

RS Models RSMI92252
Bell P-39D Airacobra with 5 decal options including RAAF, USA and USSR
Aircraft kits (injection)
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
RS Models RSMI92283
Zlin Z-XIII include TWO kits. OK-TBZ x 2, YU-PFR, and P6. Single seat and twin seat aircraft
Aircraft kits (injection)
Catalogue
£24.50
-
In Stock
10+

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
RS Models RSMI92284
Heinkel He-46C German reconnaissance Decals 4 x Luftwaffe
Aircraft kits (injection)
Catalogue
£24.50
-
In Stock
10+

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
RS Models RSMI92285
Heinkel He-46 E-2Un Hungarian reconnaissance Decals 4 x Hungary
Aircraft kits (injection)
Catalogue
£24.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

No Image
RS Models RSMI92286
Heinkel He-46C in Spanish service Decals 4 x Spanish
Aircraft kits (injection)
Catalogue
£24.50
-
In Stock
10+

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
RS Models RSMI92287
Heinkel He-46C German reconnaissance Decals 3 x Luftwaffe 1 x Hungary
Aircraft kits (injection)
Catalogue
£24.50
-
In Stock
10+

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
RS Models RSMI94015
Bucker Bu-131D 'Jungmann' GG+GK and KI+YU The Bƒ¼cker Bƒ¼ 131 Jungmann was a German two-seat biplane trainer used during World War II. The aircraft was designed by Swedish-born designer Ing. Anders J. Anderson, who led all design work at Bƒ¼cker until 1939. 1. Bƒ¼cker 131D, GG+GK, Wr. Nr. 1672, Olomouc, Holice, 1941 2. Bƒ¼cker 131D, KI+YU, Wr. Nr. 1672, Olomouc, Neredin, 1942 The first flight of the prototype Bƒ¼ 131...
Aircraft kits (injection)
Catalogue
£22.99
-
In Stock
10

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
RS Models RSMI94020
Bucker Bu-131/T-131 PK-TAF, OK-TAK D-EDYD The Bƒ¼cker Bƒ¼ 131 Jungmann was a German two-seat biplane trainer used during World War II. The aircraft was designed by Swedish-born designer Ing. Anders J. Anderson, who led all design work at Bƒ¼cker until 1939. 1. Tatra T-134.5, OK-TAF, Masarykova leteckƒi liga, Olomouc-Holice, kvAºten 1938 2. Tatra T-134.9, OK-TAK, 104. kurƒ½rnƒ letka, ......ƒjen 1938 3. Bƒ¼cker 131, D-EDY...
Aircraft kits (injection)
Catalogue
£22.99
-
In Stock
10

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

RS Models RSMI92144
Re-released! North-American P-51H Mustang USAF
Aircraft kits (injection)
Catalogue
£19.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

RS Models RSMI92148
Re-released! North-American P-51H Mustang Air National Guard 5 decal variants 1. F-51H, 101st FIS, Massachusetts ANG, Logan Field, Boston, 1951 2. F-51H, 119 FIS, New Jersey ANG, Newark Airport 3. F-51H, 146 FIS, Pennsylvania ANG, ANG Gunnery Meet, 1954 4. F-51H, 133rd FIS, New Hampshire ANG, Grenier Field 5. F-51H, Texas ANG, Brooks AFB, December The P-51H was the final production Mustang and used the n...
Aircraft kits (injection)
Catalogue
£19.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

RS Models RSMI92208
Re-released! North-American NA-64 'Yale' 1. R.C.A.F., Borden February 1942, Canada 2. R.C.A.F., No.6 SFTS Dunnville June 1942, Canada 3. R.C.A.F., No.1 SFTS Camp Borden 1940, Canada 4. R.C.A.F., No.6 SFTS Dunville July 1942, Canada The North-American NA-64 (NA-64 P-2 or NAA-64 P-2 in French service, Yale in Canadian service) is a low-wing single piston engine monoplane advanced trainer aircraft that was built fo...
Aircraft kits (injection)
Catalogue
£19.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

RS Models RSMI92153
1:72
Back in stock! Henschel Hs-132A - with BMW 003 engine 5 decal variants 1. Hs-132A, StG 77 /alternate marking/ 2. Hs-132 V1 3. Hs-132A, KG 51 /alternate marking/ 4. Hs-132A, machine captured by Americans /alternate marking/ 5. Hs-132A, machine captured by the Red Army /alternate marking/ Henschel's Hs 132 was a World War II dive bomber and interceptor aircraft of the German Luftwaffe that never saw service. The unorth...
Aircraft kits (injection)
Catalogue
£19.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

RS Models RSMI92161
1:72
Back in stock! Marcel-Bloch MB.152 Battle of France 1940. 4 decal variants for France 1. MB-152, No. 658, Chateaunoux la Martinerie, France, May 1940 2. MB-152, No. 527, Sgt KraLƒk, Douai, France, May 1940 3. MB-152, No. 16, 6e Esc. of GCIII/9, Salon de Provence, France, 1940 4. MB-152, No. 629, 4e Esc. of GCII/9, Clermonth-Aulnat, France, 1940 The Bloch MB.152 proved the definitive production model with nearly 500 pr...
Aircraft kits (injection)
Catalogue
£19.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
RS Models RSMI92278
Lockheed F-5A Lightning 4 decal v. for USA, France
Aircraft kits (injection)
Catalogue
£24.50
-
In Stock
4

   1   |   2   |   3   |   4   |   5      »      [7]

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