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=362387 AND ps.id = 952 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 18 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 113
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
Barracuda Studios BCR24501
Supermarine Spitfire Early Style Control Column This set is for Supermarine Spitfire Mk Is and Vs fitted with machine guns only. If it has an eight gun A wing, this is the set for you. The spade grip features the round housing for the trigger, as well as an attached brake lever. This control column is offered in 3 versions. With and without gun camera button and a third version with the ailerons deflected. Perfect for your Ai...
Aircraft detailing sets (resin)
Catalogue
£13.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
Barracuda Studios BCR24502
Supermarine Spitfire Late Style Control Column This set is(designed to be used with Airfix kits)'s superb new 1/24 Supermarine Spitfire Mk IX. This stick is also suitable for every Supermarine Spitfire from The Mk Vb through the Seafire FR 47. The spade grip features the rectangular housing for the 3 position trigger switch, and a molded on brake lever. This control column is offered in 3 versions. With and without gun camera...
Aircraft detailing sets (resin)
Catalogue
£13.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
Barracuda Studios BCR24425
Hawker Hurricane Mk.I/Mk.IIC/Mk.IID 4 Slot Mainwheels - This set contains a pair of resin cast accurate mainwheels, featuring fine tire beading, readable tire data, and a super detailed wheel hubs with separate uplock lugs. Designed to replace the rubber tire/plastic wheel hub assembly in the available kits. A fast and easy upgrade for the Airfix and Trumpeter Hurricane kits. Patterns designed by Gerry Moss (designed to be use...
Aircraft wheels (resin)
Catalogue
£13.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
Barracuda Studios BCR24435
1:24
Messerschmitt Bf-109E/F Mainwheels with Ribbed Tires - This set contains a pair of accurate resin mainwheels featuring super detailed spoked hubs, subtly ribbed tires and readable logo and data as fitted to the Bf-109E and F series aircraft. Replace the problematic rubber tires found in both the Airfix and Trumpeter Bf-109E kits. Patterns designed by Gerry Moss
Aircraft wheels (resin)
Catalogue
£13.99
-
In Stock
6

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
Barracuda Studios BCR24449
Supermarine Spitfire Mk.Ia/Mk.Vb/Mk.VI - Mk.IXc Five Slot Mainwheels. This set contains a pair of resin cast accurate mainwheels, featuring fine tire beading, readable tire data, and a super detailed 5-slot wheel hubs. Designed to replace the rubber tire/plastic wheel hub assembly found in the Airfix and Trumpeter Spitfire Mk. I and V kits. A fast and easy upgrade. Patterns designed by Gerry Moss (designed to be used with Airf...
Aircraft wheels (resin)
Catalogue
£13.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
Barracuda Studios BCR24450
Supermarine Spitfire Mk.Ia/Mk.Vb/Mk.VI - Mk.IX This set consists of a pair of resin cast accurate mainwheels, featuring fine tire beading, readable tire data, and a super detailed covered 5-slot wheel hubs. The covered hubs were used to keep mud and dirt out of the wheels hubs. Designed to replace the rubber tire/plastic wheel hub assembly found in the Airfix and Trumpeter Spitfire Mk. I and V kits. A fast and easy upgrade. Pa...
Aircraft wheels (resin)
Catalogue
£13.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

Barracuda Studios BC-24167
Re-printed! de Havilland Mosquito NF.II/FB Mk.VI Airframe Stencils - Expanded. This set is the result of new research based on the superb Mosquito restorations being done by Avspecs in NZ. The external stencils are extensive, and we include new instrument faces for the instrument panel, a few cockpit placards and even some decals for the radios. Printed by Cartograf, these are a real upgrade to the kit stencils. With complete ...
Aircraft decals (military)
Catalogue
£11.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

Barracuda Studios BCR24412
Grumman F6F-6 Hellcat Diamond Tread Wheels This set contains new resin mainwheels featuring tires with accurate diamond tread pattern, logos and tyre data. The wheels also incorporate detailed hubs and separate hubcaps for an excellent 3 dimensional look. Diamond tread tires are the most common type fitted, and could be seen in any theater. A simple and very visible upgrade (designed to be used with Airfix kits)
Aircraft wheels (resin)
Catalogue
£13.99
-
In Stock
6

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

Barracuda Studios BCR24413
Grumman F6F-6 Hellcat Cross Tread Wheels. This set contains new resin mainwheels featuring tyres with accurate crucifix style tread pattern, logos and tyre data. The wheels also incorporate detailed hubs and separate hubcaps for an excellent 3 dimensional look. Cross tread tires were not as common, but were used widely in the Fleet Air Arm and USN as well. A simple and very visible upgrade for the Airfix 1/24th scale Hellcat. ...
Aircraft wheels (resin)
Catalogue
£13.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

Barracuda Studios BCR24271
1:24
RAF Ground-crew Assembling Rocket. This set of crew figures represent two RAF Armourers assembling a 3" rocket as fitted to Typhoons, Mosquitos, Beaufighters and other RAF aircraft during WWII. They are stunningly detailed and artfully posed by Vince Chmielewski. They have been completely designed in CAD, and the detail must be seen in person to be fully appreciated. The set consists of two crewman, a 3" rocket with 60 lb. HE ...
Figures (resin)
Catalogue
£43.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

Barracuda Studios BCR24295
1:24
Messerschmitt Bf-109G-10 and Bf-109k-4 Wheels
Aircraft wheels (resin)
Catalogue
£13.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

Barracuda Studios BCR24325
1:24
Messerschmitt Bf-109G ribbed ribbed wheels (designed to be used with Trumpeter kits) This set contains a pair of super detailed, accurate resin mainwheels as fitted to the Bf-109G-4 thru G-14 series aircraft. This wheel features the ribbed hub and treaded tires, as seen on the vast majority of these Gustavs until later in the war. Replaces the problematic soft vinyl/plastic composite wheels that come with the Trumpeter kits. B...
Aircraft wheels (resin)
Catalogue
£13.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

Barracuda Studios BCR24200
1:24
Hawker Typhoon Mk.Ib seat with NO seat belt (designed to be used with Airfix kits)
Aircraft seats (resin)
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

Barracuda Studios BCR24281
1:24
de Havilland Mosquito Late Main wheels (designed to be used with Airfix kits) These new resin wheels are a huge upgrade to the soon to the Airfix Mosquito in 1/24th scale. They represent the late, dual brake style wheel fitted to all but the early B. Mk.IVs and NF.IIs, and feature detailed and accurate tread pattern, hub detail and tire/tyre data. The Airfix kit is very nice, but the wheels are poor, with monster truck style t...
Figures (resin)
Catalogue
£16.99
-
In Stock
9

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

Barracuda Studios BCR24204
1:24
Hawker Typhoon Mk.Ib seat with belt (designed to be used with Airfix kits)
Aircraft seats (resin)
Catalogue
£11.99
-
In Stock
9

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

Barracuda Studios BCR24201
1:24
Hawker Typhoon Mk.Ib main wheels (designed to be used with Airfix kits)
Aircraft wheels (resin)
Catalogue
£13.99
-
In Stock
6

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

Barracuda Studios BCR24202
1:24
Hawker Typhoon Mk.Ib carb intake (designed to be used with Airfix kits)
Aircraft detailing sets (resin)
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

Barracuda Studios BCR24208
1:24
Hawker Typhoon Mk.Ib standing pilot figure mid-war (designed to be used with Airfix kits)
Figures (resin)
Catalogue
£25.99
-
In Stock
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