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

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 GE32074
Hawker Hunter tow bar (designed to be used with the Revell kits) of the Hawker Hunter FGA.9/Mk.58.
Diorama accessories (resin)
Catalogue
£10.80
-
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

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

No Image
AIM - Ground Equipment GE24025E
RE-RELEASED! Trolley Accumulator: (Early Version) 3D-Printed
Diorama accessories
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 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

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 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 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 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 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 GE32078
Eurofighter Typhoon tow bars " two-pack, with wheels up and down (3D-Printed resin parts). Designed to be used with the Trumpeter kits of the Eurofighter EF-2000A Typhoon single seat or Eurofighter EF-2000B Typhoon twin seat. [EF-2000A, EF-2000B]
Diorama accessories
Catalogue
£18.00
-
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 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 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+

   1   |   2   |   3   |   4   |   5      »      [5]

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