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 pt.id=1899
		 AND P.product_category_id = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 190 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
Special Armour SA72027
Captured Sd.Kfz.250/1 Ausf.A USUALLY é£14.20. TEMPORARILY SAVE 1/3RD!!! Alte Ausfƒ¼hrung) 1/72 The earlier version of the Sd.Kfz.250 with a hexagon-shaped body armour was one of the most widely used German WWII armoured personnel carriers. The type was in production and used since 1941. In 1943, the vehicles begun to be subsequently replaced by a later type which allowed easier production, the Sd.Kfz.250/1 Neue Ausf.A. Both ve...
Military vehicle kits (injection)
Special Offers
£9.47
-
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
Magic Factory MF2005
MRZR D4 Ultralight Tactical All-Terrain Vehicle (Dual Combo/Two vehicles in one kit) USUALLY é£49.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£37.49
-
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
UM-MT UMMT699
Chemical (Flame-Throwing) Tank HBT-5 WAS é£11.20. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£7.47
-
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
Unimodel UNIM457
1:72
M36B1 90mm gun motor carriage WAS é£18.40. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£12.27
-
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
I LOVE KIT LK61619
M4A3E8 Sherman Medium Tank Early Sherman "Easy Eight" with detail up set. USUALLY é£239.99. TEMPORARILY SAVE 1/3RD!! Tank, Medium, M4, to use the official US Army Ordnance Department nomenclature, underpinned American armoured forces during the Second World War and the Korean War, and is regarded as one of the most important military vehicles ever made. Better known as the "General Sherman", or simply "Sherman", more than 49...
Military vehicle kits (injection)
Special Offers
£159.99
-
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
I LOVE KIT LK61620
US M4A3E8 Sherman Easy Eight, WWII Medium Tank Late USUALLY é£169.99. TEMPORARILY SAVE 1/3RD!! Sherman "Easy Eight" with detail up set. Tank, Medium, M4, to use the official US Army Ordnance Department nomenclature, underpinned American armoured forces during the Second World War and the Korean War, and is regarded as one of the most important military vehicles ever made. Better known as the "General Sherman", or simply "She...
Military vehicle kits (injection)
Special Offers
£113.33
-
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
I LOVE KIT LK63514
M923A2 US Military Cargo Truck 5 Ton 6x6 USUALLY é£79.99. TEMPORARILY SAVE 1/3RD!!
Military vehicle kits (injection)
Special Offers
£53.33
-
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
I LOVE KIT LK63515
M925A1 US Military Cargo Truck 5 Ton 6x6 USUALLY é£79.99. TEMPORARILY SAVE 1/3RD!! M939 series all-terrain all-weather general service truck with dropside body and canvas cover. Front mounted winch. 8 sprues, cab and tyres. Length: 241.9mm, Width: 93.4mm. Total parts 350+
Military vehicle kits (injection)
Special Offers
£53.33
-
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
I LOVE KIT LK63516
1:35
M3A1 Medium Tank WWII US Army Lee with cast upper hull. USUALLY é£63.99. TEMPORARILY SAVE 1/3RD!!
Military vehicle kits (injection)
Special Offers
£42.66
-
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
I LOVE KIT LK63522
M65 280mm Atomic Cannon 'Atomic Annie' USUALLY é£199.99. TEMPORARILY SAVE 1/3RD!!
Military vehicle kits (injection)
Special Offers
£133.33
-
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
I LOVE KIT LK63526
US C-RAM with HEMTT A3 USUALLY é£129.99. TEMPORARILY SAVE 1/3RD!! US Army Counter Rocket, Artillery and Mortar (C-RAM) "Centurion" land-based Phalanx rapid-fire, computer-controlled radar and 20mm gun counter-insurgent system which automatically intercepts rockets, artillery and mortar rounds in the air before impact. C-RAM has defended the Green Zone US base in Baghdad Iraq against more than 100 hostile incoming rockets and ...
Military vehicle kits (injection)
Special Offers
£86.66
-
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
I LOVE KIT LK63527
US M1001 with M790 Erector Launcher Trailer USUALLY é£159.99. TEMPORARILY SAVE 1/3RD!! German MAN 27.365VFAE KAT-1A1 M1001 8-wheel drive prime mover, towing M790 erector launcher (EL) bogie semi-trailer, carrying Pershing II ballistic missile, operated by US Army and German Air Force in Western Europe during the Cold War. 26 sprues, cab & tyres, photo-etch parts. L: 447.2mm, W: 87.6mm, Total parts 960+
Military vehicle kits (injection)
Special Offers
£106.67
-
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
I LOVE KIT LK63531
M48A1 USUALLY é£84.99. TEMPORARILY SAVE 1/3RD!! used by USA's Cold War allies, especially other NATO countries. 8 sprues, upper & lower hull & tracks. Choice of 7 markings. L: 244.2mm, W: 103.7mm, Total parts 540+
Military vehicle kits (injection)
Special Offers
£56.66
-
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
I LOVE KIT LK63537
M1278A1 Heavy Guns Carrier mod M153 CROWS USUALLY é£79.99. TEMPORARILY SAVE 1/3RD!! US Army Joint Light Tactical Vehicle (JLTV) with Common Remote Weapon Station (CROWS) based on Oshkosh Light Combat Tactical All-Terrain Vehicle (L-ATV). 8 sprues, cab & tyres. L: 160.7mm, W: 92.4mm, Total parts 390+
Military vehicle kits (injection)
Special Offers
£53.33
-
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
Tamiya TA32603
Pz.Kpfw.VI Tiger I Early Production East Front USUALLY é£25.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£17.33
-
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
Hobby Boss HB80151
Pz.Kpfw.III/IV (Small Turret) USUALLY é£46.99. TEMPORARILY SAVE 1/3RD!!! in March 1944, Wa Pruef 6 awarded contracts for the production of three trial Pz.Kpfw.IV auf Einheitsfahrgestell. A decision was made in June 1944 to start Pz.Kpfw.III/IV series production at Krupp-Grusonwerk in February 1945. On 12 July 1944, Krupp was informed that the Pz.Kpfw.III/IV was dropped and only the Pznaerjaeger mit L/70 produced
Military vehicle kits (injection)
Special Offers
£31.33
-
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
Hobby Boss HB82929
48N6E 5P85S TEL S-300OMU SA-10 Grumble USUALLY é£49.99. TEMPORARILY SAVE 1/3RD!!! The S-300 (NATO reporting name SA-10 Grumble) is a series of Russian long range surface-to-air missile systems produced by NPO Almaz, based on the initial S-300P version. The S-300 system was developed to defend against aircraft and cruise missiles for the Soviet Air Defence Forces. Subsequent variations were developed to intercept ballistic miss...
Military vehicle kits (injection)
Special Offers
£33.33
-
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

No Image
Hobby Boss HB82934
Schwere Plattformwagen Type SSyms 80 AND Pz.Kpfw.VI Ausf.E Sd.Kfz.181 Tiger I (Mid Production) USUALLY é£40.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£27.33
-
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
Hobby Boss HB82939
Soviet (9P117 M1) Launcher with R17 (Scud B) USUALLY é£40.99. TEMPORARILY SAVE 1/3RD!!! The first use of the term Scud was in the NATO name SS-1b Scud-A, applied to the R-11 ballistic missile. The R-11 used technology gained from the V-2 as well, but was a new design, smaller and differently shaped than the V-2 and R-1 weapons. The R-11 was developed by the Korolyev OKB[1] and entered service in 1957. The most revolutionary in...
Military vehicle kits (injection)
Special Offers
£27.33
-
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
Hobby Boss HB84542
PLA 59-1 Medium Tank USUALLY é£40.99 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£27.33
-
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

No Image
Hobby Boss HB85520
LKW 7t Dump Truck German Army USUALLY é£74.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£49.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 Boss HB85522
LKW 7t mil gl USUALLY é£74.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£49.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

No Image
Hobby Boss HB85526
M1070 Dump Truck USUALLY é£86.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£57.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

No Image
Hobby Boss HB85529
9M96 of 50P6E TEL S350E Vitazƒšé USUALLY é£124.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£83.33
-
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

Panda PH35038
M1 IP Abrams MBT USUALLY é£49.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£33.33
-
In Stock
10+

   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