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=309417 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 61 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

AIM - Transport Wings TWC72035W
RE-RELEASED! Douglas DC-7C wheel set (designed to be used with Heller Douglas DC-6 kits and Transport Wings DC-7 Conversion set)
Aircraft wheels (resin)
Catalogue
£7.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

AIM - Transport Wings TWC72041
RE-RELEASED! de Havilland DH-104 Dove & Devon seats - 3D-Printed resin parts (designed to be used with the A Model Dove & Devon kits)
Aircraft seats (resin)
Catalogue
£9.90
-
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

AIM - Transport Wings TWC72043
RE-RELEASED! Dart 510 engine (with bulged cowling) - set of four engines. To convert to Vickers Viscount 700D (designed to be used with Mach 2 Vickers Viscount 700 kits)
Aircraft conversions (resin)
Catalogue
£13.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

AIM - Transport Wings TWC72055
RE-RELEASED! WE177B & C (long body) - British nuclear weapon series. For use with models of the Avro Vulcan, de Havilland Sea Vixen, Blackburn Buccaneer, SEPECAT Jaguar and the Panavia Tornado. (designed to be used with Airfix kits)
Aircraft bombs and rockets (resin)
Catalogue
£7.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

AIM - Transport Wings TWC72040
Back in stock! Supercharged Gipsy Queen engines - for Dove-7/8 & Devon C2 - (designed to be used with the A Model Dove & Devon kits)
Aircraft conversions (resin)
Catalogue
£12.60
-
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

AIM - Transport Wings TWC72051
Re-released! Yellow Sun nuclear weapon. Yellow Sun was the first British operational high-yield strategic nuclear weapon warhead. The name refers only to the outer casing; (Designed for use with the Airfix kit of the Vickers Valiant). (designed to be used with Airfix and Mach 2 kits)
Aircraft bombs and rockets (resin)
Catalogue
£13.05
-
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

AIM - Transport Wings TWC72052
Back in stock! Blue Danube - British nuclear weapon series. For use with models of the Vickers Valiant B1, Avro Vulcan B1 and the Handley Page Victor B1. (designed to be used with Airfix kits)
Aircraft bombs and rockets (resin)
Catalogue
£16.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

No Image
AIM - Transport Wings TWC72074
12,000 lb blockbuster bomb. One of the largest conventional bombs used in World War II, three quarters of the weight of this thin walled bomb was explosives. (Designed for use with the Airfix or Revell kits of the Avro Lancaster B.I/III ).
Aircraft detailing sets (resin)
Catalogue
£9.00
-
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
AIM - Transport Wings TWC72075
Tallboy bomb. One of the largest conventional bombs used in World War II, this 12,000lb bomb was designed by Dr. Barnes Wallis to penetrate deep into the earth before exploding, thus causing an earthquake. (Designed for use with the Airfix or Revell kits of the Avro Lancaster B.I/III ).
Aircraft detailing sets (resin)
Catalogue
£9.00
-
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
AIM - Transport Wings TWC72073
1:72
8,000 lb blockbuster bomb.. One of the largest conventional bombs used in World War II, three quarters of the weight of this thin walled bomb was explosives. (Designed for use with the Airfix or Revell kits of the Avro Lancaster B.I/III ).
Aircraft detailing sets (resin)
Catalogue
£5.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

AIM - Transport Wings TWC72053
Re-released! Red Beard - British nuclear weapon series. For use with model of the English Electric Canberra and the V bombers of the Royal Air Force, and by the Blackburn Buccaneers, de Havilland Sea Vixens, and Supermarine Scimitars of the Fleet Air Arm (designed to be used with AMP, Ark Models, Airfix, Eastern Express, FROG, High Planes, Italeri, Micro-Mir, Novo, Matchbox, Plastyk, Revell , S & M Models and Xtrakit kits)[BAC...
Aircraft bombs and rockets (resin)
Catalogue
£4.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
AIM - Transport Wings TWC72061L
Back in stock! USAF/USN B61-12 current nuclear weapon twin pack. Two weapons with maintenance/transportation dollies - these are highly detailed 3D-Printed resin parts.
Aircraft detailing sets (resin)
Catalogue
£14.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

No Image
AIM - Transport Wings TWC72063
MXU-648 Travel Pod (Early) " This pack contains two highly detailed 3D-Printed resin pods, one with an open door (idea for a diorama), and the other with a closed door. They can be used with many types of aircraft, but individual research is recommended.
Aircraft detailing sets (resin)
Catalogue
£10.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

AIM - Transport Wings TWF72002
Air Rhodesia Douglas C-47A (circa 1968) decal set.
Aircraft decals (civil)
Catalogue
£10.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

No Image
AIM - Transport Wings TWC72059
Legion Pod - Multi-function Sensor System (2 pod pack)
Aircraft detailing sets (resin)
Catalogue
£10.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

No Image
AIM - Transport Wings TWC72072
1:72
Lockheed-Martin F-35A Lightning II 426 gal under-wing fuel tanks - 3D-Printed parts - two tank pack. (designed to be used with Academy, Hasegawa, Italeri, PM Model and Revell kits).
Aircraft detailing sets (resin)
Catalogue
£11.70
-
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
AIM - Transport Wings TWC72056
ACMI P5 Pods (2 per pack)
Aircraft bombs and rockets (resin)
Catalogue
£7.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

No Image
AIM - Transport Wings TWC72058
Myasishchev 3M Bison or VM-T Altlant Wheel set, 3D-Printed resin parts (designed to be used with A-Model kits).
Aircraft wheels (resin)
Catalogue
£18.00
-
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

AIM - Transport Wings TWC72044
1:72
RAE Vickers Viscount 837 Radar development aircraft (For use with the Mach 2 Vickers Viscount kit and Transport Wings Vickers Viscount 800 conversion set) (designed to be used with Mach 2 Vickers Viscount 700 kits)
Aircraft conversions (resin)
Catalogue
£11.70
-
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

AIM - Transport Wings TWC72047
Vickers Viscount 800/810 Slipper Tanks (large)(For use with the Mach 2 Vickers Viscount 700 kit and Transport Wings Vickers Viscount 800 conversion set) (designed to be used with Mach 2 Vickers Viscount 700 kits)
Aircraft conversions (resin)
Catalogue
£10.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

AIM - Transport Wings TWL72070
British European Airways (BEA) Vickers Viscount 701 - Delivery scheme - circa 1953 decal set (designed to be used with the Mach 2 Viscount 700 kits).
Aircraft decals (civil)
Catalogue
£15.12
-
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

AIM - Transport Wings TWL72071
1:72
British European Airways (BEA) Vickers Viscount 802 - Delivery scheme - circa 1957 decal set (designed to be used with Mach 2 Viscount 700 kit and Transport Wings Viscount 800 conversion set).
Aircraft decals (civil)
Catalogue
£15.12
-
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

AIM - Transport Wings TWL72075
1:72
Northeast Airlines (UK) Vickers Viscount 806 - Circa 1972 (designed to be used with the Mach 2 Viscount 700 kit and Transport Wings Viscount 800 conversion set)
Aircraft decals (civil)
Catalogue
£15.12
-
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

AIM - Transport Wings TWC72036
1:72
Douglas DC-6/DC-7 non-radar nose (designed to be used with Heller Douglas DC-6 kits)
Aircraft conversions (resin)
Catalogue
£4.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

AIM - Transport Wings TWF72026
1:72
Air Trans Africa Douglas DC-7F 'Sanctions Buster' (circa 1970)
Aircraft decals (civil)
Catalogue
£15.30
-
In Stock
2

   1   |   2   |   3      »      [3]

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