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.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 25 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
Delivered 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
Phoenix Scale Publications R2RRED04
Republic F-105 Thunderchief by Andy Evans
Aircraft books
New Arrivals
01/05/2024
£27.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
Tamiya TAMXF092
No Scale
Yellow-Brown Dak 1941
Paint, Acrylic 10ml
New Arrivals
25/04/2024
£2.25
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
Tamiya TAMXF093
Light Brown Dak 1942
Paint, Acrylic 10ml
New Arrivals
25/04/2024
£2.25
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
IBG Models CATIBG24
IBG Catalogue 2024
Catalogues and lists
New Arrivals
23/04/2024
£1.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
Start HIF02
Army in Focus 2.
Military vehicle books
New Arrivals
19/04/2024
£27.80
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
ADH Publishing MIM152
Military Illustrated Modeller (issue 152) 4 NEWS Military scale-modelling news 8 SPOTTING LEOPARDS Tamiyas new 1:35 Leopard 2A7V, previewed 12 STRYKER, LIGHT! The US Armys M1128 Mobile Gun System in 1:72aâé-¦ 20 NUREMBERG TOY FAIR 2024 MIM reports from the biggest toy fair in the world Cover story 28 REAP THE WIRBELWIND A masterclass on the creation of a whitewash on Dragons 1:35 Wirbelwind 38 THE BLACK SA...
Magazines
New Arrivals
19/04/2024
£6.95
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
GA Publishing AW2003
Avro Anson C.19 walkround.
Aircraft books
New Arrivals
10/04/2024
£14.00
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
ADH Publishing MIM151
Military Illustrated Modeller (issue 151) 4 NEWS New release announcements 6 HEINKEL OVER BRITAIN ICM 1:48 scale Heinkel He- 111H-3 by Gary Edmundson 18 PREVIEW Airfix 1:72 Gloster Gladiator Cover story 20 CLASSIC WILDCAT Pt. 2 Mark Casiglias Tamiya 1:48 scale Grumman F4F-4 Wildcat 36 GREEN PEARL OF THE SOLOMON ISLANDS Pt. 2 Josef Blazeks Eduard 1:48 scale Nakajima A6M2-N Rufe 50 PREVIEW Kotare 1:32 Spitf...
Magazines
New Arrivals
02/04/2024
£6.95
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

Airdoc ADSD004
No Scale
BACK IN STOCK!!! Jadgbombergeschwader 35 - JG 73, JaboG 42, LeKG 42 & JG 73(F-F4) by Daniel Kehl 132 pages, English and German, approx. 240 photos, bound together and limited to 500 pieces. The chronicle of Jagdbombergeschwader 35 in horse field complete in picture and word - our two issues ADL 011 and 012 tied together in a book.
Aircraft books
New Arrivals
24/03/2024
£31.80
-
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 VWPAA-20
AM-20 The Heinkel He-177 Greif A Detailed Guide To The Luftwaffes Troubled Strategic Bomber by Richard A. Franks This is our biggest Airframe Album to date at 192+ pages. Our twentieth title in the Airframe Album series will be an essential reference for any Luftwaffe enthusiast and anyone tackling the Revell or other kits in 1/72nd and 1/48th scale. The Heinkel He 177 Greif contains: aâé-¢ A wealth of hist...
Aircraft books
New Arrivals
13/03/2024
£25.95
£23.36
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
Dragon CATDN24
Dragon Catalogue 2024
Catalogues and lists
New Arrivals
07/03/2024
£4.75
£4.28
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
Tamiya CATTA24
2024 Tamiya Catalogue
Modelling books
New Arrivals
07/03/2024
£16.99
£15.29
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
Zvezda CATZVE24
Zvezda Catalogue 2024
Catalogues and lists
New Arrivals
07/03/2024
£7.50
£6.75
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
SAM Publications MDFSD12
MDFSD12 Dassault Mirage F1
Aircraft books
New Arrivals
05/03/2024
£17.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
Phoenix Scale Publications R2RWHITE01
No Scale
Re-printed! The McDonnell F-4A/F-4B/F-4N/F-4J/F-4S & RF-4B Phantom US Navy and Marine Corps Versions By Andy Evans 84 Pages Full Colour The iconic F-4 Phantom is one of the most recognisable aircraft ever produced. Initially built for the US Navy, its multi-role abilities as an interceptor, fighter-bomber and reconnaissance platform were quickly adopted by the Marine Corps and first entered service in 1961. So impress...
Aircraft books
New Arrivals
29/02/2024
£15.99
£14.39
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
Phoenix Scale Publications R2RWHITE05
The McDonnell-Douglas Phantom (British Versions) FG.1, FGR.2 AND F-4J (UK) By Andy Evans 84 Pages Full Colour
Aircraft books
New Arrivals
29/02/2024
£15.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
Naval Fighters NF117
USN / USMC Fairchlid R4Q / C-119 "Packet" / "Flying Boxcar" Available February 24 By Nicholas M. Williams 72-pages, 140 b&w photos, 5 color photos, 22-patches, and 19 drawings. In 1949 the US Navy was looking for a tactical assault transport for its Marine Corps units and arrangements were made to divert 8 off the shelf C-119Bs from the USAF to the USMC. These 8 aircraft and the 31 C-119Cs that followed were designat...
Aircraft books
New Arrivals
26/02/2024
£24.99
£22.49
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
ADH Publishing MIM150
Military Illustrated Modeller Issue 150 4 NEWS Military scale-modelling news 8 RECON RUNNER Gecko Model 1:35 Daimler Armoured Car Mk.II, previewed 10 THE FLYING TELEGRAPH POLE IN VIETNAM NVA SAM launcher and missile in 1:35 20 BLACK ROSIES: FIGHTING THE WAR AND RACISM AT HOME An evocative scene that pays tribute to the 600,000 African American women involved in war-work Cover story 28 GAME OVER BATTL...
Magazines
New Arrivals
16/02/2024
£6.95
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
Dutch Profile DDP110
Consolidated PBY-5/A Catalina Part 2 MLD 1945-1958
Aircraft books
New Arrivals
14/02/2024
£14.99
£13.49
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
Dutch Profile DDP42
No Scale
Dutch Presentation Spitfires
Aircraft books
New Arrivals
14/02/2024
£14.99
£13.49
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
Dutch Profile DDP45
Grumman Tracker MLD
Aircraft books
New Arrivals
14/02/2024
£14.99
£13.49
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
Dutch Profile DDP46
Consolidated PBY-5/A Catalina Part 1 MLD 1941-1945
Aircraft books
New Arrivals
14/02/2024
£14.99
£13.49
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
Dutch Profile DDP48
Lockheed PV-2 Harpoon MLD By Nico Geldhof, production Luuk Boerman 40 pages FC with many profiles of this famous aircraft
Aircraft books
New Arrivals
14/02/2024
£14.99
£13.49
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
Dutch Profile DDP62
Mitchell en Mosquito 320 Sqn RAF Mitchell and (Mosquito 1945-1946) in service with 320 Sqn RAF 1942-1945.
Aircraft books
New Arrivals
14/02/2024
£14.99
£13.49
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
ADH Publishing MIM149
Military Illustrated Modeller Issue 149 4 NEWS New release announcements 6 CLASSIC WILDCAT Pt.1 Mark Casiglias Tamiya 1:48 scale F4F-4 Wildcat 20 PREVIEW Red Roo Models 1:48 Moth Minor in RAAF Service 22 REFERENCE A6M2 Zero 26 BIG ZERO Tamiya 1:32 A6M2b Zero by Gary Edmundson Cover story 40 GUSTAV IN GREEN Pt.2 Revells 1:32 Bf-109 G-2 by Brett Green 52 PREVIEW Airfix 1:72 Messerschmitt Me 410 56 THE...
Magazines
New Arrivals
07/02/2024
£6.95
Out of Stock
0


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