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=362460 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 39 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

Czech Master Resin CMR-DS05
1:72
Beaching Trolley for Hansa-Brandenberg W.20 flying boat. (designed to be used with Czech Master Resin kits) (Hansa W.20/2]
Diorama accessories (resin)
Catalogue
£7.25
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

Czech Master Resin CMR72-217
Re-released! de Havilland Vampire FB.31 in RAAF Service. Colour Schemes Included de Havilland Vampire FB.31 - A79-215 No.21 Squadron, RAAF, 1957 de Havilland Vampire FB.31 - A79-111 'Red Devils' Aerobatic Team, No.2 O.T.U., RAAF, Williamtown, N.S.W., circa 1958 de Havilland Vampire FB.31 - A79-915 No.21 Squadron, RAAF, Laverton, Victoria, September 1959 de Havilland Vampire FB.31 - A79-550 '50', RAAF (Squadron, date & p...
Aircraft kits (resin)
Catalogue
£47.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

Czech Master Resin CMR72-5105
Grumman G-21 Goose flying boat. Grumman G-21 Goose I - 942 ' 'MK-H', No.13 (OT) Squadron, RCAF, Vancouver, B.C., March 1941 Grumman JRF-2 ' U.S. Coast Guard, 1942 Grumman JRF-5 ' U.S. Navy, July 1943
Aircraft kits (resin)
Catalogue
£35.70
-
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

Czech Master Resin CMR72-140
Westland Wyvern TF.1 with Rolls-Royce Eagle 24 cylinder 'H' configured engine with decals Westland Wyvern N.11/44 - TS380 (the fourth Mk.1 Prototype), December, 1948 Westland Wyvern Prototype - TS371 Yeovil Factory Base, December 1948 Westland Wyvern TF.1 - VR131 Westland Aircraft Ltd., Yeovil, Somerset, March 1949
Aircraft kits (resin)
Catalogue
£40.80
-
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

Czech Master Resin CMR72-112
Morane-Saulnier A1 with decals Morane-Saulnier Type A.1 - MS 1591 Escadrille MSP 156, 1918. Flown by: Sgt. Walter J. Shaffer Morane-Saulnier Type A.1 - MS 1724 Escadrille MSP 158, March/April 1918. Flown by: Sgt. Rufus R. Rand Jnr.
Aircraft kits (resin)
Catalogue
£29.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

Czech Master Resin CMR72-G5021
Slingsby Kirby Cadet Mk.1 with decals (gliders)
Aircraft kits (resin)
Catalogue
£18.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

Czech Master Resin CMR72-132
1:72
Re-released! Westland Welkin Mk.I re-issue, decals included
Aircraft kits (resin)
Catalogue
£45.30
-
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

Czech Master Resin CMR72-G5016
1:72
Goppingen Go-3/DFS Minimoa with decals (gliders) GĦppingen G 3 'Minimoa' - 'die 100 Minimoa', *Nationalsozialistisches Fliegerkorps (NSFK), Germany, June 1939 GĦppingen G 3 'Minimoa' - S-42 Post-war 'British Air Force of Occupation' gliding club, 1945 GĦppingen G 3 'Minimoa' - 15-790 Contest number '52', D-STADT-NECKARGEMUND GĦppingen G 3 'Minimoa' - D-7-2192 (Czecholsovak designation VT-2), c/n 305, *'strredni let...
Aircraft kits (resin)
Catalogue
£21.80
-
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

Czech Master Resin CMR72-5005
Hansa-Brandenberg W.20/2 flying boat. Now with decals! (Also available to buy separately CMR-DS05 Beaching Trolley for this kits)
Aircraft kits (resin)
Catalogue
£24.75
-
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

Czech Master Resin CMR72-011
1:72
Siemens-Schuckert D.III (this kit does not have any decals included)
Aircraft kits (resin)
Catalogue
£50.60
-
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

Czech Master Resin CMR72-123
1:72
Supermarine Spitfire Prototype with decals Supermarine Type 300, Supermarine Spitfire Prototype - K5054 As she appeared in June 1936
Aircraft kits (resin)
Catalogue
£28.80
-
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

Czech Master Resin CMR72-170
Supermarine Spitfire Type 300 prototype, Eastleigh Airport 1935 'Birth Of A Legend' Supermarine Type 300, Supermarine Spitfire Prototype - K5054 'Maiden Flight', Eastleigh Airport, 5th March 1936
Aircraft kits (resin)
Catalogue
£34.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

Czech Master Resin CMR72-236
Supermarine Spitfire Prototypes. F.21 Victor PP139 & F.23 Valiant LA187. This full resin kit contains 46 parts, two clear parts, vacu canopy (a spare set is included for modeller's convenience), and a coloured photo-etched fret. The decal sheet also includes a set of stencils. Colour schemes included in the kit: 1) Spitfire F Mk.21 'Victor' prototype, PP139, Aeroplane & Armament Experimental Establishment, Boscombe Down, ...
Aircraft kits (resin)
Catalogue
£32.60
-
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

Czech Master Resin CMR72-154
1:72
Supermarine Spitfire Mk.XII
Aircraft kits (resin)
Catalogue
£34.60
-
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

Czech Master Resin CMR72-G109
1:72
Opel-Sander RAK.1 glider with decals Opel-Sander RAK.I Rocket Glider Early 'Blue' trim Opel-Sander RAK.I Rocket Glider Later 'Red' trim (gliders)
Aircraft kits (resin)
Catalogue
£19.80
-
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

Czech Master Resin CMR72-5070
1:72
CAC CA-15 NOW WITH DECALS!
Aircraft kits (resin)
Catalogue
£29.20
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

Czech Master Resin CMR72-239
1:72
Curtiss F9C Sparrowhawk "Airship Fighter"
Aircraft kits (resin)
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

Czech Master Resin CMR72-009
1:72
Armstrong F.K.10
Aircraft kits (resin)
Catalogue
£50.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

Czech Master Resin CMR72-G014
1:72
Grunau Baby I (gliders)
Aircraft kits (resin)
Catalogue
£11.30
-
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

Czech Master Resin CMR72-G028
1:72
Horton Ho-IV (new mould) (gliders)
Aircraft kits (resin)
Catalogue
£17.85
-
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

Czech Master Resin CMR72-G040
1:72
Aeronca TG-5 USAAF (gliders) December 2014 with new decals
Aircraft kits (resin)
Catalogue
£21.80
-
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

Czech Master Resin CMR72-G041
Horton Ho-IVb (gliders)
Aircraft kits (resin)
Catalogue
£17.85
-
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

Czech Master Resin CMR72-G5020
1:72
Fafnir 2 Sao Paulo (gliders)
Aircraft kits (resin)
Catalogue
£12.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

Czech Master Resin CMR72-5082
1:72
Pfalz Dr.I with decals Pfalz Dr.I - 3050/17 'Flying Trials', October to December 1917 - Pfalz factory, Speyer, Germany Pfalz Dr.I - 222/17 Jasta 73, Western Front, Spring 1918
Aircraft kits (resin)
Catalogue
£50.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

Czech Master Resin CMR72-G5013
Goppingen Go-4 with decals (gliders) Gƒ¶ppingen Gƒ¶-4 - D-15-1350 October 1941 Gƒ¶ppingen Gƒ¶-4 - D-9-763 1943 Gƒ¶ppingen Gƒ¶-4 - D-13-436 1943 Gƒ¶ppingen Gƒ¶-4 - OK-9249 Hanƒickƒ½ Aeroklub Olomouc (Czechoslovak National Aeroclub in Olomouc), June 1947
Aircraft kits (resin)
Catalogue
£21.80
-
In Stock
1

   1   |   2      »      [2]

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