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_category_id = 103
		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 asc
Future Releases | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Future Releases



Found 2,479 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

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

No Image
MiniArt MT35378
MARCH RE-RELEASE! GERMAN SOLDIERS AT REST. SPECIAL EDITION Box Contains Models of Five Figures, Water Pump, Barrels, Chair, Jug, Wash Tub, Pot on Bricks and Shaving Accessories.
Figures (injection)
Future Releases
£13.99
£12.59
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
MiniArt MT35381
MARCH RE-RELEASE! 7.5 CM PZGR. & GR. PATR. KW.K. 40 SHELLS WITH AMMO BOXES
Diorama accessories (injection)
Future Releases
£13.99
£12.59
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

MiniArt MT35603
MARCH RE-RELEASE! Tool box set with etched parts. Includes spanners, pliers, fire extinguisher, Tilly lamp, spade, shovel, crow-bar, pick-axe, axe, hammer, plane, adjustable spanner, saws, mallet, vice, screw driver, clamp, anvil, oil cans, jack etc, etc.
Diorama accessories (injection)
Future Releases
£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

MiniArt MT39001
MARCH RE-RELEASE! B-TYPE MILITARY OMNIBUS (Old Bill Bus) HIGHLY DETAILED MODEL ACCURATE ENGINE CLEAR PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 5 VARIANTS
Military vehicle kits (injection)
Future Releases
£57.99
£52.19
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
Meng Model MMSPS-091S
BMW R 1250 GS ADV Luggage (Pre-Coloured)
Motorbike detailing sets
Future Releases
£18.99
£17.09
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

Italeri IT1235
General-Dynamics EF-111A Raven Decals for 27th TFW and 388th TFW NEW SUPER DECALS SHEET FOR 2 VERSIONS - COLOR INSTRUCTION SHEET FIGURE NOT INCLUDED The General Dynamics EF-111 Raven was developed on the aerodynamic platform of the ground-attack aircraft F-111 Aardvark. The supersonic aircraft, featuring variable-geometry wings, was identified as a suitable platform to accommodate the electronic components necessary to ...
Aircraft kits (injection)
Future Releases
£TBA
£TBA
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
Mark I Decals DMF14423
Bristol Brigand (TF.I, B.1, MET.3, T.4, T.5)(designed to be used with Valom kits)
Aircraft decals (military)
Future Releases
£11.20
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
Mark I Decals DMF7205
Bristol Brigand (TF.I, B.1, MET.3, T.4, T.5)(designed to be used with Valom kits)
Aircraft decals (military)
Future Releases
£12.85
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
DK Decals DKD32042
Bell P-39/P-400 Airacobra 1. P-39N Airacobra, Capt. G.A. Hilbert, 41st FS, 35th FG, New Guinea 1943 2. P-400 Airacobra, AP356, Lt. J. Kurt, 35th FS, 8th FG, New Guinea 1942/43 3. P-39Q, 42-19993, Lt. P. McDermott, 82nd TRS, 71st TRG, New Guinea 1944
Aircraft decals (military)
Future Releases
£22.50
£20.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
DK Decals DKD48055
WWII RAAF Twins Pt.2: Bristol Beaufighter, Douglas Boston, North-American Mitchell 1. Beaufighter Mk.21, A8-14, No.31 Sq. RAAF, Morotai 1945é 2. A-20G Boston, A28-60, No.22 Sq. RAAF, New Guinea 1944é 3. B-25J Mitchell, A47-27, No.2 Sq. RAAF, Australia 1944
Aircraft decals (military)
Future Releases
£13.99
£12.59
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
DK Decals DKD48056
WWII RAAF Twins Pt.3: Ventura, Anson, Catalina 1. PV-1 Ventura, A59-89, No.13 Sq. RAAF, Australia 1944é 2. Anson Mk.I, AX261, No.73 Sq. RAAF, Australia 1944é 3. PBY-5A Catalina, A24-112, ASR Flight, Australia 1945é
Aircraft decals (military)
Future Releases
£13.30
£11.97
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
DK Decals DKD48057
WWII RAAF Twins Pt.4: Wellington, Ventura, Baltimore 1. Wellington Mk.VIII, HX509, No.458 (RAAF) Sq., MTO 1943é 2. Ventura GR Mk.V, FP612, No.459 (RAAF) Sq., MTO 1943é 3. Baltimore Mk.IV, FA603, No.454 (RAAF) Sq., MTO 1943é
Aircraft decals (military)
Future Releases
£13.99
£12.59
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
DK Decals DKD48064
Bell P-39/P-400 Airacobra 1. P-400 Airacobra, BW146, 67th FS, 347th FG, Guadalcanal 1942 2. P-39N Airacobra, 42-18812, Lt. E.W. Kyro, 41st FS, 35th FG, New Guinea 1943 3. P-39D, 41-38338, Lt. D.C. McGee, 36th FS, 8th FG, New Guinea 1942/1943é 4. P-39Q, 42-19993, Lt. P. McDermott, 82nd TRS, 71st TRG, New Guinea 1944é 5. P-39D, 41-38401, Maj. N.G. Morris, New Guinea 1942/43é 6. P-39D Airacobra, 333rd FS, 318th FG, Hawai...
Aircraft decals (military)
Future Releases
£20.50
£18.45
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
DK Decals DKD48065
Early Strafers 'The Grim Reapers' 1942-43 1. A-20A Havoc, 40-101, 89th BS, New Guinea 1942é 2. A-20A Havoc, 40-3151, 89th BS, New Guinea 1942 3. A-20A Havoc, 40-170, 89th BS, New Guinea 1942é 4. North-American B-25C Mitchell, 41-12946, 90th BS, Australia 1942é 5. North-American B-25C Mitchell, 41-12437, 90th BS, New Guinea 1943é 6. North-American B-25D Mitchell, 41-30766, 13th BS, New Guinea 1943é
Aircraft decals (military)
Future Releases
£19.40
£17.46
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
DK Decals DKD48066
North-American B-25C/D Mitchell 'The Grim Reapers' 1942 1. North-American B-25C Mitchell, 41-12962, 13th BS, Australia 1942 2. North-American B-25D Mitchell, 41-29709, 13th BS, New Guinea 1942 3. North-American B-25C, 41-12443, 13th BS, 1/Lt. R.A. Price, New Guinea 1942 4. North-American B-25C, 41-12449, 90th BS, Capt. W.R. Johnson, New Guinea 1942 5. North-American B-25D Mitchell, 41-29737, 13th BS, Australia 1942é 6. ...
Aircraft decals (military)
Future Releases
£18.40
£16.56
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
DK Decals DKD48067
A-20A Havoc 'The Grim Reapers' 1942 1. A-20A Havoc, 40-146, 89th BS, Lt. Langley, New Guinea 1942é 2. A-20A Havoc, 40-173, 89th BS, Lt. J.B. Roan, New Guinea 1942é 3. A-20A Havoc, 40-143, 89th BS, New Guinea 1942é 4. A-20A Havoc, 40-109, 89th BS, New Guinea 1942 5. A-20A Havoc, 40-139, 89th BS, New Guinea 1942 6. A-20A Havoc, 40-094, 89th BS, Ch. Mayo, New Guinea 1942
Aircraft decals (military)
Future Releases
£19.40
£17.46
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

DK Decals DKD72005
RE-PRINTED!!! Dornier Do-24T & Catalina in RAAF and NEIAF (2 + 6 camouflage schemes) 1. Consolidated PBY-5A Catalina, A24-91, No.111 ASR Flight, 1945 2. Consolidated PBY-5A Catalina, A24-64, No.43 Sq. RAAF, Australia 1943 3. Consolidated PBY-4 Catalina, A24-29, No.20 Sq. RAAF, New Guinea 1943 4. Consolidated PBY-5A Catalina, A24-65, No.43 Sq. RAAF, Australia 1943 5. Consolidated PBY-5A Catalina, A24-17, No.11 Sq. RAAF, 19...
Aircraft decals (military)
Future Releases
£17.40
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
Revell RV3355
Leopard 2A7V
Military vehicle kits (injection)
Future Releases
£22.99
£20.69
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
Revell RV3793
1:72
Boeing B-52D Stratofortress Platinum Edition
Aircraft kits (injection)
Future Releases
£129.49
£116.54
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
Revell RV3801
Tornado 50th Anniversary Twinpack
Aircraft kits (injection)
Future Releases
£41.99
£37.79
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
Revell RV3805
Westland Lynx
Aircraft kits (injection)
Future Releases
£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
Revell RV3806
Westland Lynx - Special Paintwork
Aircraft kits (injection)
Future Releases
£49.99
£44.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
Revell RV5180
German Submarine Type IXC/40 Platinum Edition
Ship kits, waterline hull (injection)
Future Releases
£159.99
£143.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
Revell RV5242
KFK Kriegsfischkutter, WWII German multi-purpose boat ex-ICM
Ship kits, full hull (injection)
Future Releases
£32.49
£29.24
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
Revell RV5643
Gift Set North-American B-25J Mitchell: Flying Bulls 25th Anniversary
Aircraft kits (injection)
Future Releases
£49.99
£44.99
Out of Stock
0

[1]   «      37   |   38   |   39   |   40   |   41      »      [100]
-20 -10 | +10 +20 +50

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