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 = 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 57 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 GE72037
Re-released! Avro LANCASTER & LINCOLN CREW LADDERS (designed to be used with Airfix and Revell kits) 3D-Printed
Diorama accessories (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 - Ground Equipment GE72084
RAF Sepecat Jaguar tow bars " two pack (wheels up & down (esigned to be used with the Airfix, Hasagawa and Italeri kits) 3D-Printed
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 GE72090
T300 Pay Mover tractor with cab (USAF) US Air Force T300 Paymover tractor. Used to move large aircraft, and designed to be used with the Monogram or Revell B-52, and Anigrand C-5 Galaxy or Boeing 747 or VC-25 kits, in conjunction with the appropriate AIM Ground Equipment tow bar. 3D-Printed
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 GE72094
USAF tow bar for Convair B-36H/RB-36F Peacemaker (designed to be used with Monogram and Revell kits) 3D-Printed
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 GE72125
Rockwell B-1 Lancer tow bars - two pack (wheels up and wheels down) 3D-Printed (designed to be used with Airfix, Monogram and Revell 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 GE72180
1:72
USAF fighter revetment USAF fighter revetment set of the Vietnam era (plus A-10 and F-16 later in Korea). It can be used with aircraft types which included the: A-1, A-37, A-7, F-4, F-5, F-100, F-105, F-111 (plus A-10 and F-16 later in Korea). The set includes 9 wall sections, 9 joiners and 2 end panels. 3D-Printed
Diorama accessories (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

AIM - Ground Equipment GE72054
RE-RELEASED! Avro Vulcan B.2 Tow Bar (V-Bomber support series) (designed to be used with Airfix kits) 3D-Printed
Diorama accessories (resin)
Catalogue
£13.95
-
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 GE72038
USAF Peacekeeper armoured security vehicle 3D-Printed
Military vehicle kits (resin)
Catalogue
£16.20
£14.58
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 GE72078
Back in stock! Eurofighter Typhoon tow bars " two-pack, with wheels up and down (3D-Printed resin parts). Designed to be used with the Airfix, ESCI/ERTL, Hasagawa, Hobby Boss, Italeri or Revell kits of the Eurofighter EF-2000A Typhoon single seat or Eurofighter EF-2000B Typhoon twin seat, and the AIM-Ground Equipment kit of the RAF F-59 medium tractor.
Diorama accessories
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 GE72077
Tucano tow bars " two-pack, with wheels up and down (3D-Printed resin parts). Designed to be used with the Airfix kit of the Short Tucano, and the AIM-Ground Equipment kit of the RAF F-59 medium tractor.
Diorama accessories
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 GE72097
C-130 Hercules Tow Bar (3D-Printed resin parts). Designed to be used with the Airfix or Italeri kits of the C-130 Hercules, and the AIM-Ground Equipment kit of the RAF F-396 large tractor.
Diorama accessories
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 GE72103
Fairey Gannet tow bar (3D-Printed resin parts). Designed to be used with the Frog, Novo or Revell kits of the Fairey Gannet.
Diorama accessories
Catalogue
£6.30
-
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 GE72071
Back in stock! BAe Hawk T.1 Towbar (designed to be used with Airfix and Italeri kits)
Diorama accessories
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 GE72064
Re-released! Ground Power Plinths - V bomber support series - New model using 3D-Printed with enhanced details. (designed be used with Airfix kits) [Vickers Valiant Mk.1 Avro Vulcan Handley-Page Victor B.2]
Diorama accessories (resin)
Catalogue
£11.70
-
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 GE72089
Re-released! Royal Navy F-59N deck tractor & fire engine . This is a highly detailed 3D-Printed resin item (it is designed be used with models of any current Royal Navy small or medium sized aircraft, and the appropriate AIM ground equipment tow bar).
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 GE72062
RAF Mandator Blue Steel missile transporter. A highly detailed 3D-Printed model with movable derrick and outrigger legs. (designed to be used with Airfix kits of the Avro Vulcan B.2 and Handley-Page Victor B.2 with Blue Steel missile) 3 (designed to be used with Airfix kits)[Avro Vulcan B.2 Handley-Page Victor B.2 with Blue Steel missile]
Diorama accessories
Catalogue
£57.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

AIM - Ground Equipment GE72059
1:72
Re-released! Gloster Meteor Tow Bar (designed to be used with the Airfix kit of the Gloster Meteor F.8, Frrom-Azur kit of the Gloster Meteor T Mk.7, MPM kit of the Gloster Meteor PR.10, or the Special Hobby kits of the Gloster Meteor F.4, Gloster Meteor F.8/Mk.9, Gloster Meteor T Mk.7, Gloster Meteor T 7.5, Gloster Meteor NF.11, Gloster Meteor NF.12, or the Gloster Meteor NF Mk.14, or the Xtrakit kit of the Gloster Meteor F.8 ...
Diorama accessories (resin)
Catalogue
£5.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 GE72083
Blackburn Buccaneer S.2B/S.2C tow bars- wheels up and down (pack of 2) (designed to be used with Airfix kits)
Diorama accessories
Catalogue
£10.80
-
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

No Image
AIM - Ground Equipment GE72012
1:72
RAF MD300 Tractor
Diorama accessories (resin)
Catalogue
£28.80
-
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 - Ground Equipment GE72055
Re-released! 60 kva Ground Power Unit (GPU). Re-tooled for 3D printing, to give a highly detailed model. (designed to be used with Airfix kits)[of the Avro Vulcan - all versions, Handley Page Victor - all versions, and Vickers Valiant - all versions] (designed to be used with Airfix kits)[Avro Vulcan B.2 Handley-Page Victor B.2 ]
Diorama accessories (resin)
Catalogue
£18.90
-
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 GE72095E
Boeing B-52 tow bar early version x 2 in a pack
Diorama accessories
Catalogue
£19.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 GE72081
1:72
Lockheed SR-71 Blackbird tow bar, used with a MB-4 tractor (GE72016L). This is a 3D-Printed resin item -(designed to be used with Academy, Hasegawa, Italeri, Monogram and Revell kits)
Diorama accessories (resin)
Catalogue
£10.80
-
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

No Image
AIM - Ground Equipment GE72088
Royal Navy F-59N deck tractor . This is a highly detailed 3D-Printed resin item (it is designed be used with models of any current Royal Navy small or medium sized aircraft, and the appropriate AIM ground equipment tow bar).
Diorama accessories (resin)
Catalogue
£22.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 GE72085
1:72
F-59 tractor (current RAF medium tractor)
Diorama accessories
Catalogue
£26.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 GE72031R
Lockheed A-12 and Lockheed SR-71 Blackbird Crew Access Steps (designed to be used with Academy, Italeri and Revell kits) Re-tooled 3D-Printed model, which replaces the original etched brass kit GE72031
Diorama accessories (resin)
Catalogue
£18.00
Out of Stock
0

   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