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 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 84 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

No Image
AIM - Transport Wings TWC48070
RE-RELEASED! Gulf War Tornado - Full load set. 8x 1000lb, 4x Bomb carrier.
Aircraft bombs and rockets (resin)
Catalogue
£27.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

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

No Image
AIM - Transport Wings TWC32073
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 to be used with Border Models and Hong Kong Models kits)[B.I/III B.III Lancaster B.Mk.I/III]
Aircraft detailing sets (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

No Image
AIM - Transport Wings TWC32074
1:32
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 to be used with Border Models and Hong Kong Models kits)[B.I/III B.III Lancaster B.Mk.I/III]
Aircraft detailing sets (resin)
Catalogue
£18.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 TWC32075
1:32
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 to be used with Border Models and Hong Kong Models kits)[B.I/III B.III Lancaster B.Mk.I/III]
Aircraft detailing sets (resin)
Catalogue
£18.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 TWC32076
Grand Slam bomb. The largest conventional bomb used in World War II, this 22,000lb bomb was designed by Dr. Barnes Wallis to penetrate deep into the earth before exploding, thus causing an earthquake. (designed to be used with Hong Kong Models kit)[Avro Lancaster B Mk.I Special 'Grand Slam']
Aircraft detailing sets (resin)
Catalogue
£21.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 TWC48053
Re-released! Re-released! Red Beard - British nuclear weapon series. Red Beard was the first British tactical nuclear weapon. It was carried by the V-bomber force and other aircraft. (Designed to be used with Airfix kits of the BAC/English Electric Canberra and the Blackburn Buccaneer S.2C/D, or the Blackburn Buccaneer S.2B, Trumpeter kits of the de Havilland Sea Vixen FAW.2, and Sanger kits of the Supermarine Scimitar).
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

AIM - Transport Wings TWC48055
Re-released! WE177B & C (long body) nuclear weapon The WE177 was the primary air-dropped nuclear weapon in the United Kingdom from the late 1960s into the 1990s. (Designed for use with the Airfix kits of the Avro Vulcan, de Havilland Sea Vixen, Blackburn Buccaneer, SEPECAT Jaguar, and Panavia Tornado).
Aircraft bombs and rockets (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 TWC48074
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 Tamiya kit of the Avro Lancaster B Mk.I/III ).
Aircraft detailing sets (resin)
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

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 TWC48073
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 Tamiya kit of the Avro Lancaster B Mk.I/III )
Aircraft detailing sets (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 TWC48075
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 Tamiya kit of the Avro Lancaster B Mk.I/III ).
Aircraft detailing sets (resin)
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

No Image
AIM - Transport Wings TWC48076
1:48
Grand Slam bomb. The largest conventional bomb used in World War II, this 22,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 Tamiya kit of the Avro Lancaster B Mk.I/III ).
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 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 TWC48060
Back in stock! AN/ASQ-236 Dragon's Eye Radar pod " two pod pack of 3D-Printed parts. (designed to be used with Academy, Great Wall Hobby and Italeri kits of the F-15E Strike Eagle, Academy, Kinetic Model Kits and Tamiya kits of the Lockheed-Martin F-16C, and Hasegawa and Kinetic Model Kits of the F-16D).
Aircraft detailing sets (resin)
Catalogue
£21.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 TWC48001
RAFMcDonnell Phantom FGR.2 LS-93A Flash Pod conversion set. (designed to be used with Hasegawa and Revell kits)
Aircraft detailing sets (resin)
Catalogue
£8.10
-
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 TWC48063
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
£16.20
-
In Stock
5+

   1   |   2   |   3   |   4      »      [4]

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