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 ps.id = 89514
		 AND P.product_category_id = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



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

Scale

Product Type

Manufacturer

Added Within

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

Valiant Wings Publishing VWP777392
AM-09 Airframe & Miniature No 9 Dornier Do-335 Pfeil by Richard A. Franks. Updated and expanded! aâé-¢ 40+ pages of technical information aâé-¢ 20+ pages of walk-around images and technical diagrams aâé-¢ 5+ pages of camouflage and markings aâé-¢ 25+ pages of model builds and modelling information aâé-¢ 200+ photographs including wartime images aâé-¢ Colour side views and four-views by Richard J. Caruana aâé-¢ 3D isometric views of ...
Aircraft books
Limited Availability
£18.95
-
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

Valiant Wings Publishing VWP9575866
AC-02 Building The 1:32nd Heinkel He-219 (designed to be used with Zoukei Mura kits)
Aircraft books
Limited Availability
£13.95
-
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
Valiant Wings Publishing VWPAM-01R
AM-01R Messerschmitt Me-262. A Complete Guide To The Luftwaffe's First Jet Fighter. (Owing to the volume of extra material we have increased the book size from 176 to 208 pages plus covers and plans) Valiant Wings Publishing's next title returns to where it all started ten years ago with a second, much expanded edition of our very first title The Messerschmitt Me-262 - A Complete Guide To The Luftwaffe's First Jet Fighter b...
Aircraft books (on modelling aircraft)
Limited Availability
£24.95
-
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
Guideline Publications FFIATA1
Fantasy Figures Book After the Apocalypse
Military vehicle books
Limited Availability
£19.99
-
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
Hobby 2000 H2K80001
Precision Masking Tape 1mm x 18m
Paint masking material
Limited Availability
£2.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

Iliad Design ILCC05
No Scale
Polish Air Force Late 1930s and 1939 Campaign
Aircraft paint colour charts
Limited Availability
£11.99
-
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

Shesto POL1000
No Scale
Precision Lubricator
Modelling tools (metal)
Limited Availability
£4.95
-
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

Shesto RC9001
No Scale
25 piece Sanding and Shaping Set. Will fit all makes of mini rotary tools
Modelling tools (metal)
Limited Availability
£11.95
-
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

Seaforth Publishing SFP539-3
No Scale
Italian Naval Camouflage of World War II 9781526735393 Hardback by Marco GhiglinoPublished: July 2018
Ship books
Limited Availability
£35.00
-
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

Swann Morton SM9311
No Scale
No.24 Blade to fit SM9106 No.2 and SM9107 no.5 handle in pack of 50 blades.
Modelling tools (metal)
Limited Availability
£30.99
-
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

Trumpeter TU09834
No Scale
84mm x 47mm Turntable with 116mm flat top dome (Requires 1 x 1.5v AA Battery)
Bases and cases
Limited Availability
£12.99
-
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

Trumpeter TU09864
No Scale
84 x 185mm Flat Top Display Case with optional LED base
Bases and cases
Limited Availability
£13.99
-
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

CMK/Czech Master Kits CMKSD02
No Scale
Light Rust weathering pigments
Weathering products
Limited Availability
£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

CMK/Czech Master Kits CMKSD12
No Scale
Black weathering pigments (Star Dust range)
Weathering products
Limited Availability
£4.50
-
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

CMK/Czech Master Kits CMKSD13
No Scale
Dirty White weathering pigments (Star Dust range)
Weathering products
Limited Availability
£4.50
-
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

Start LIF18
No Scale
Luftwaffe im Focus Edition No 18 aâé-¢ 50 pages, about 65 photos - thereof 10 in colour, 4 colour profiles, 2 maps, Readerforum aâé-¢ Aircraft in Focus: Me-262 'black D' of 2./KG 51 in February 1945 aâé-¢ Photos with a story: Of "Crabs", "Snakes" and "Dƒ¶deln" (The story of 3. Staffel JGr.10 in 1945, Part 2) aâé-¢ Tail: Ship killer in the Arctic Sea -Lt. Hennemann 2./KG 26 aâé-¢ Colourphotos: The 1.500 mission of 3.(H)/Aufklƒ¤...
Aircraft books
Limited Availability
£17.80
-
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

Start LIF19
No Scale
Luftwaffe im Focus Edition No 19 aâé-¢ 50 pages, about 67 photos - thereof 8 in colour, 3 colour profiles, 1 coloured map, 1 coloured document, 4 coloured emblems, Readerforum aâé-¢ Yellow or orange the never-ending discussion: Colour slides provide new insight aâé-¢ Aircraft in Focus: The Junkers Ju-88 Flamethrower Aircraft of I./KG 51 aâé-¢ The Staffel Emblem of 3./JG52: One of the last unknown fighter emblems is identi...
Aircraft books
Limited Availability
£17.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

Start LIF23
No Scale
Luftwaffe im Focus Edition No.23 aâé-¢ 50 pages, 52 photos - thereof 7 in colour, 2 colour profiles, 1 coloured emblem, 3 coloured maps, Readerforum aâé-¢ Scenery: Hit in the Cockpit! (JG26) aâé-¢ Colour Photos of a Seldom-Seen Kampfgeschwader (I./KG 28) aâé-¢ Aircraft in Focus: An Extraordinary Aircraft from the Defense of the Reich The Fw-190 of Major Philipp, Geschwaderkommodore of JG1 aâé-¢ Unknown Emble...
Aircraft books
Limited Availability
£17.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

Polish Wings PWB25-9
No Scale
Polish Wings 25 - Fokker E.V/D.VIII - Tomasz Kopanski
Aircraft books
Limited Availability
£15.00
-
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

Polish Wings PWB50-1
Polish Wings No. 27 French Flying Boats 1918-1939 - Marcin Warzynowski.
Aircraft books
Limited Availability
£15.00
-
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

Polish Wings PWB80-7
No Scale
Polish Wings No. 23. 303 Squadron North-American Mustang. No. 303 Squadron was the only Polish unit equipped with the Mustang IV, as the bubble-top P-51D and P-51K were known collectively to the RAF. Between April 1945 and December 1946, the squadron used a total of 29 of these aircraft, plus six Mustang I's as hacks. The book includes over 120 photos and nearly 30 colour plates to profusely illustrate these aircraft....
Aircraft books
Limited Availability
£15.00
-
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
Polish Wings PWB87-7
Polish Wings No. 28 Tupolev Tu-2, Sukoi UTB-2 and Shcherbakov Shche-2
Aircraft books
Limited Availability
£15.00
-
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

Start STARTDAS
Das vergessene As. Der Jagdfliger Gerhard Barkhorn by Bend Barbas The Forgotten Ace Fighter Pilot Gerhard Barkhorn Despite the fact that Gerhard Barkhorn scored a total of 301 victories while flying with Jagdgeschwader 52 on the Eastern Front, making him the second most successful fighter pilot of the Second World War after Erich Hartmann, today he is almost forgotten. This book describes Barkhorn's wartime experiences w...
Aircraft books
Limited Availability
£54.99
-
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

Start UBF06
U-Boot im Focus Edition No 6 aâé-¢ 50 pages, 58 photos - thereof 3 colour-photos, 3 colour-conning tower sideviews, 1 coloured emblem, 1 coulered document, Readerforum aâé-¢ U-Boat in focus: U-404 The "Viking Ship" on a Patrol with an Underwater Camera aâé-¢ Unusual: U-178 with Wheels! aâé-¢ Photos with a Story: The Forgotten Negatives The Only Photos of U-215 aâé-¢ Conning Tower: U-702 The Boat with the Elephant's...
Ship books
Limited Availability
£17.80
-
In Stock
2

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

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