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 P.product_category_id = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



Found 9 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

Barracuda Studios BCR32062
1:32
Heinkel He-219A-7 Uhu Diamond Tread Mainwheels (designed to be used with Revell kits) This set consists of 4 superbly accurate and detailed resin mainwheels with authentic diamond tread that even replicates the narrower center row of diamonds. Wheels also feature fine raised tire/tyre beads and extremely fine raised tire/tyre size information. This set also features separate pressure fittings and prepainted black wire to simul...
Aircraft wheels (resin)
Limited Availability
£15.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 BCR48279
1:48
Grumman S-2 Tracker Early Mainwheels (designed to be used with Kinetic Model kits) The Kinetic Grumman S-2 Tracker kits come with late style main wheels as fitted to all Trackers after the retrofit to upgrade the brakes was completed in the 1970s. Before that, all Trackers were fitted with these early style wheels. Available for the first time in 1/48th scale, Barracuda Studios' wheels are nicely detailed, accurate and easy to...
Aircraft wheels (resin)
Limited Availability
£7.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 BCR32016
(Ltd) North-American P-51D Mustang Corrected Paper Drop tanks (designed to be used with Tamiya kits) Super-detailed, seamless nose and tailcones for Tamiya's 108 gallon drop tanks capture the rough, scalloped look of the paper mache. The paper drop tanks in the Tamiya Mustang kit are accurate in shape and size, but they do not capture the 'look' of the end caps on the real tanks. The real tanks are made of compressed, impregna...
Aircraft detailing sets (resin)
Limited Availability
£10.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 BCR32030
(Ltd) American P-51D Mustang Oval Tread Pattern Tyres/Tires (designed to be used with Tamiya kits) This set contains new oval tread pattern tires/tyres that directly replace the tires/tyres in the Tamiya kit. The problem with the soft vinyl tires/tyres in the kit is that the seams are very difficult to remove, and they tend to attract dirt and dust. These seamless resin parts have superb tread detail, and are both sandable and...
Aircraft wheels (resin)
Limited Availability
£8.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 BCR32059
1:32
(Ltd) Republic P-47C/P-47D Thunderbolt Block Tread Mainwheels . This set contains a pair of detailed resin main wheels with crisply molded, seamless and accurate block tread pattern. You can choose between plain, covered or 6 slot hubs. The inner wheel hub detail is visible through the slots of the outer wheel hub, bringing a whole new level of detail. Makes a quick, easy and very visible upgrade (designed to be used with Hase...
Aircraft wheels (resin)
Limited Availability
£8.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 BCR32067
(Ltd) Messerschmitt Me-262 Main, Plain Hub Nosewheels
Aircraft wheels (resin)
Limited Availability
£12.99
-
In Stock
8

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 BCR32072
(Ltd) Messerschmitt Bf-109G Wheels - Plain Hub, Ribbed Tire/tyre. This set consists of a pair of super detailed, accurate resin mainwheels as fitted to the Bf-109G-5 through G-14 series aircraft. These wheels represent the later fit of the ribbed tire/tyre fitted to the plain, ribless hub. Finely rendered raised tire/tyre data is featured. Will upgrade any 1/32nd Bf-109G kit, such as Hasegawa, Revell, and Trumpeter kits. (desi...
Aircraft wheels (resin)
Limited Availability
£9.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 BCR32121
(Ltd) Vought F4U Corsair, Grumman F6F Hellcat Wheels - Stamped Hub. Super detailed resin wheels with the late production stamped wheel hub and ribbed tires/tyres as fitted to late model F6F, F4U, Warbirds, and Air Racers. A simple upgrade for all Corsair and Hellcat kits (designed to be used with Trumpeter kits) [F6F-5N F6F-3N F6F-3 F6F-5 F4U-1D F4U-4]
Aircraft wheels (resin)
Limited Availability
£7.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 BCR48007
Hawker Sea Fury FB.11 90 gallon drop tanks wheel (designed to be used with Hobbycraft and Trumpeter kits) This set consists of a pair of resin cast 90 gallon droptanks to replace the inaccurate kit parts in the Trumpeter and Hobbycraft Sea Fury kits. The Trumpeter tanks are both misshapen and grossly oversized. Designed as a drop fit replacement for the Trumpeter Sea Fury kit.
Aircraft detailing sets (resin)
Limited Availability
£5.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