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=362453 AND ps.id = 955 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 37 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 - Ground Equipment GE48090
T300 Pay Mover Tractor with cab (USAF) US Air Force T300 Paymover tractor. (designed to be used with the Revell Rockwell B-1B Lancer or the Lockheed SR-71 A Blackbird kits, or the Classic Airframe B-57 kits) 3D-Printed
Diorama accessories (resin)
Catalogue
£45.00
-
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
AIM - Ground Equipment GE48125
Rockwell B-1 Lancer Tow bars - two pack (wheels up & down) (designed to be used with Monogram and Revell kits) 3D-Printed
Diorama accessories (resin)
Catalogue
£16.20
-
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
AIM - Ground Equipment GE48110
RE-RELEASED! Hawker Hunter F.6 & BAe Harrier GR.3 Crew Ladder (designed to be used with Academy, Airfix and Italeri kits) 3D-Printed
Diorama accessories (resin)
Catalogue
£8.10
-
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
AIM - Ground Equipment GE48038
USAF Peacekeeper armoured security vehicle, which was introduced in the early eighties. The vehicles were also used by Indonesia, Luxemberg and The Phillippines, plus various police forces. This is a high-quality 3D-Printed resin model with a detailed interior.
Military vehicle kits (resin)
Catalogue
£39.60
-
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
AIM - Ground Equipment GE48071
Bback in stock! BAe Hawk Towbar (designed to be used with Airfix kits) BAe Hawk tow bars " two-pack, with wheels up and down (3D-Printed resin parts). Designed to be used with the Airfix, Italeri or Hobby Boss kits of the BAe Hawk, and the AIM-Ground Equipment kit of the RAF F-59 medium tractor.
Diorama accessories
Catalogue
£12.60
-
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
AIM - Ground Equipment GE48078
1:48
Eurofighter Typhoon tow bars " two-pack, with wheels up and down (3D-Printed resin parts). Designed to be used with the Hasagawa, Italeri, or Revell kits of the Eurofighter EF-2000A/EF-2000B Typhoon, and the AIM-Ground Equipment kit of the RAF F-59 medium tractor.
Diorama accessories
Catalogue
£12.60
-
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
AIM - Ground Equipment GE48097
Back in stock! C-130 Hercules tow bar (3D-Printed resin model). Designed to be used with the Italeri kit of the Lockheed C-130J Hercules and the AIM-Ground Equipment kit of the RAF F-396 large tractor.
Diorama accessories (resin)
Catalogue
£10.80
-
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
AIM - Ground Equipment GE48103
Fairey Gannet tow bar (3D-Printed resin parts). (designed to be used with Airfix kits) of the Fairey Gannet AS.1/AS.4[Airfix AS.1/AS.4]
Diorama accessories (resin)
Catalogue
£9.00
-
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
AIM - Ground Equipment GE48045L
Back in stock! Ejector Seat Servicing Trolley - Ejector seats are mounted on these trolleys, when they are removed from the aircraft. NEW TOOL - previously an etched brass kit GE48045, it has been redesigned to give a high-quality 3D-Printed resin model. TYPE - 3D-Printed resin diorama accessory
Aircraft seats (resin)
Catalogue
£7.20
-
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
AIM - Ground Equipment GE48031R
Lockheed A-12 and SR-71 Blackbird Crew Access Steps (designed to be used with Italeri and Revell kits) Re-tooled 3D-Printed model, which replaces the original etched brass kit GE48031
Diorama accessories (resin)
Catalogue
£43.50
-
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
AIM - Ground Equipment GE48083
Blackburn Buccaneer S.2C/D tow bars- wheels up and down (pack of 2) (designed to be used with Airfix kits)
Diorama accessories
Catalogue
£14.40
-
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

No Image
AIM - Ground Equipment GE48121
Switchblade 600 Unmanned Air Vehicle (UAV) - also known as a loitering drone. This is a highly detailed, 3D-Printed model of the munition used so successfully in the Ukraine. The kit consists of two UAVs, one with flying surfaces stowed, and the other with flying surfaces deployed, plus a loaded launch tube.
Diorama accessories (resin)
Catalogue
£14.40
-
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

AIM - Ground Equipment GE48016L
USAF MB-4 tractor (late version) which was widely used with the United States Air Force (USAF) in the seventies, eighties and later. This is a highly detailed 3D-Printed resin model with full interior. Suitable for use with the 1:48 Academy & Hobby Boss F-111, and Italeri & Revell SR-71 kits, plus other aircraft of the period. (designed be used with Academy, Hobby Boss, Italeri and Revell kits) etc
Diorama accessories (resin)
Catalogue
£28.80
-
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
AIM - Ground Equipment GE48037L
Avro Lancaster & Avro Lincoln crew Ladder Set.
Diorama accessories (resin)
Catalogue
£7.20
-
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
AIM - Ground Equipment GE48081
Lockheed SR-71 Blackbird tow bar, used with a MB-4 tractor (GE48016L). This is a 3D-Printed resin item (designed be used with AIM - Ground Equipment, Italeri and Revell kits)
Diorama accessories (resin)
Catalogue
£14.40
-
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
AIM - Ground Equipment GE48085
RAF F-59 medium tractor . This is a highly detailed 3D-Printed resin item (it is designed be used with models of any current RAF small or medium sized aircraft, and the appropriate AIM ground equipment tow bar) (current RAF medium tractor)
Diorama accessories
Catalogue
£26.99
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

No Image
AIM - Ground Equipment GE48072
Lockheed-Martin F-35A Lightning II (JSF) Tow Bar. 3D-Printed resin diorama accessory (designed be used with Italeri IT2810, Kitty Hawk Model KH80103, Meng Model MMLS-007, MMLS-008 or MMLS-009 kits)
Diorama accessories (resin)
Catalogue
£10.80
-
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
AIM - Ground Equipment GE48073
1:48
Gloster Javelin Tow Bar - 3D-Printed resin diorama accessory, for use with Airfix AX12007
Diorama accessories (resin)
Catalogue
£11.70
-
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

No Image
AIM - Ground Equipment GE48087
RAF Hallam HE50 tractor - highly detailed 3D-Printed resin kit with full interior - suitable for use with Italeri IT2813, and AIM - Ground Equipment GE48071 towbar, plus many more aircraft types.
Diorama accessories (resin)
Catalogue
£30.60
-
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
AIM - Ground Equipment GE48301
RAF aircrew - standing - 60s/70s - set of six 1:48 3D-Printed resin figures
Figures (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

No Image
AIM - Ground Equipment GE48074
Re-released! Hunter Tow Bar 3D-Printed resin diorama accessory (designed to be used with with Academy AC12312, or Airfix AX09185 or AX09189 kits)
Diorama accessories (resin)
Catalogue
£10.80
-
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
AIM - Ground Equipment GE48075
1:48
Re-released! EE/BAC Lightning Tow Bar (2 tow bars per pack - towing wheels up or down), for use with Airfix A09179
Diorama accessories
Catalogue
£14.40
-
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
AIM - Ground Equipment GE48047E
GE-330 Lockheed Lockheed SR-71 Blackbird starter cart " Early version with Buick 401 "Nailhead" engine with square exhaust pipes. (designed to be used with Italeri, Revell and Testors kits)
Diorama accessories (resin)
Catalogue
£27.00
-
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
AIM - Ground Equipment GE48047L
GE-330 Lockheed Lockheed SR-71 Blackbird starter cart - Late version with Chevrolet 454 LS-7 engine and exhaust pipes which were widely separated underneath as individual pipes (designed to be used with Italeri, Revell and Testors kits)
Diorama accessories
Catalogue
£27.00
-
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 - Ground Equipment GE48079
BAC/EE Canberra B.2/B.20 Tow Bar (designed to be used with Airfix kits)
Diorama accessories
Catalogue
£14.40
-
In Stock
3

   1   |   2      »      [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