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=362424 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 17 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

Radial Engines & Wheels REW72021
Walter NZ-60 (designed to be used with Kovozavody Prostejov kits) [Avia B-9/BH-9 and B-11/BH-11]
Aircraft engines and propellers (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72012
1:72
Curtiss P-40E/P-40F/P-40K/P-40M/P-40N wheels - tread design as used on rough airfields (designed to be used with Airfix, Hasegawa, Heller and Revell kits)
Aircraft wheels (resin)
Catalogue
£5.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

Radial Engines & Wheels REW72015
1:72
Bristol Titan engine
Aircraft engines and propellers (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72020
1:72
Boeing B-29A Superfortress type 2 - radial tread pattern - late type wheels late (designed to be used with Academy and Airfix kits)
Aircraft wheels (resin)
Catalogue
£12.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

Radial Engines & Wheels REW72017
1:72
Armstrong Siddeley Lynx - early type
Aircraft engines and propellers (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72016
1:72
Bristol Mercury [Bristol Blenheim Mk.I Mk.IV] (designed to be used with Airfix kits)
Aircraft engines and propellers (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72013
1:72
Jumo 211 exhausts The Jumo 211 was a supercharged liquid-cooled 60-degree inverted V 12 piston aircraft engine, Junkers Motoren's primary aircraft engine of World War II. It was primarily used in HYPERLINK 'https://en.wikipedia.org/wiki/Bomber' o 'Bomber' bombers such as Junkers HYPERLINK 'https://en.wikipedia.org/wiki/Junkers_Ju_87' o 'Junkers Ju-87' Ju-87 and HYPERLINK 'https://en.wikipedia.org/wiki/Junkers_Ju_88' o 'Junk...
Aircraft exhausts (resin)
Catalogue
£3.80
-
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

Radial Engines & Wheels REW72014
1:72
Pratt & Whitney R-1340 Wasp (geared) radial engine, Suitable for use with any of the following aircraft types: Agusta-Bell AB.102 Air Tractor Ayres Thrush Bach Air Yacht Boeing Model 40A Boeing 247 Boeing F3B, F4B Boeing P-12, 26, 29 CAC Ceres CAC Wirraway Curtiss Falcon Curtiss F7C Seahawk Curtiss O-52 Curtiss P-6S Hawk Curtiss SOC Seagull de Havilland Canada DHC-3 Otter Douglas Dolphin ...
Aircraft engines and propellers (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72008
1:72
Boeing B-29A Superfortress - wheels with diamond tread (designed to be used with Academy and Airfix kits)
Aircraft wheels (resin)
Catalogue
£12.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

Radial Engines & Wheels REW72011
1:72
Curtiss P-40E Warhawk/P-40K/P-40L/P-40M/P-40N Kittyhawk Tomahawk wheels (designed to be used with Academy, Airfix, AML, Hasegawa, Heller, Hobby Boss, Smer Special Hobby and Trumpeter kits)
Aircraft wheels (resin)
Catalogue
£5.60
-
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

Radial Engines & Wheels REW72007
1:72
Consolidated B-24 Liberator - radial tread (designed to be used with Academy, Airfix, Eduard kits, Hasegawa, Italeri, Minicraft and Revell kits) [B-24D B-24H B-24J B-24L B-24M B-24N]
Aircraft wheels (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72006
1:72
Consolidated B-24 Liberator block tread pattern (designed to be used with Academy, Airfix, Eduard kits, Hasegawa, Italeri, Minicraft and Revell kits) [B-24D B-24H B-24J B-24L B-24M B-24N]
Aircraft wheels (resin)
Catalogue
£8.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

Radial Engines & Wheels REW72001
1:72
Pratt & Whitney R-1340 Wasp radial engine, Suitable for use with any of the following aircraft types: Agusta-Bell AB.102 Air Tractor Ayres Thrush Bach Air Yacht Boeing Model 40A Boeing 247 Boeing F3B, F4B Boeing P-12, 26, 29 CAC Ceres CAC CA-9 Wirraway Curtiss Falcon Curtiss F7C Seahawk Curtiss O-52 Curtiss P-6S Hawk Curtiss SOC Seagull de Havilland Canada DHC-3 Otter Douglas Dolphin Fokk...
Aircraft engines and propellers (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72002
1:72
Bristol Pegasus radial engine As used on... ANBO IV Blackburn Baffin Blackburn Ripon Blackburn Shark Boulton-Paul Mailplane Boulton-Paul Overstrand Boulton-Paul Sidestrand Bristol Bombay Bristol Type 118/120/138 Douglas DC-2 Fairey TSR.I Fairey Seal Fairey Swordfish Fokker C.X Gloster Goring Handley-Page H.P.43/47/51 Handley-Page Hampden Handley-Page H.P.54 Harrow Hawker Audax Hawker Hart Hawker Osprey ...
Aircraft engines and propellers (resin)
Catalogue
£7.50
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

Radial Engines & Wheels REW72003
Bristol Perseus radial engine As used on.... Blackburn Botha Blackburn Roc Blackburn Skua Bristol Bulldog Bristol Type 148 Cunliffe-Owen Flying Boat de Havilland Flamingo de Havilland Hertfordshire Gloster Goring Hawker Hart Saro A.23 Short S.30 Empire Flying Boat Short Scylla Vickers Vellox Vickers Vildebeest Mk.IV Westland Lysander Mk.II
Aircraft engines and propellers (resin)
Catalogue
£7.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

Radial Engines & Wheels REW72004
Boeing B-17 Flying Fortress block tread pattern (designed to be used with Academy. Airfix, Hasegawa and Revell kits) [B-17C B-17D B-17E B-17F B-17G]
Aircraft wheels (resin)
Catalogue
£6.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

Radial Engines & Wheels REW72010
1:72
Junkers Ju-290A-5/Ju-290A-7 wheels (designed to be used with Revell kits)
Aircraft wheels (resin)
Catalogue
£6.20
-
In Stock
5+


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