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 ps.id = 954 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 1,968 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
No Image
Hobby Boss HB83892
1:35
PLA ZTZ-99A MBT
Military vehicle kits (injection)
Catalogue
£35.09
Out of Stock
0
No Image
MiniArt MT35349
StuH 42 Ausf. G Early Prod. May-June 1943 HIGHLY DETAILED PLASTIC MODEL KIT HATCHES CAN BE MODELLED IN OPEN OR CLOSED POSITIONS INDIVIDUAL TRACKS INCLUDED CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED
Military vehicle kits (injection)
Catalogue
£57.99
-
In Stock
5+
No Image
MiniArt MT35355
Sturmgeschutz/StuG.42 Ausf.G LATE PRODUCTION
Military vehicle kits (injection)
Catalogue
£57.99
-
In Stock
5+
No Image
ICM ICM35136
Unimog S 404 with box body, German military truck. The Unimog S military vehicle was used by units of the Bundeswehr to solve a wide range of tasks. In addition to the standard cargo version, the version of the car with a van body also became widespread. Gias, Harmenig, Zweirad-Union, BINZ, Voll and others were engaged in the production of these closed bodies. About 16,000 cars were equipped with such body options. Many of the...
Military vehicle kits (injection)
Catalogue
£37.80
£34.02
-
In Stock
20+
No Image
ICM ICM35409
V3000S 'Einheitsfahrerhaus', WWII German *new parts. The V3000S belonged to a widespread part of the Wehrmacht truck fleet with a payload of 3 tons. The 95-hp V8 gasoline-powered truck was assembled at Ford's Cologne plant in 1941 and production continued until May 1945. It was used by all branches of Germany military forces - the Army, Air Force and Navy. Cooling of the V8 engines was quite problematic, so later versions were...
Military vehicle kits (injection)
Catalogue
£37.80
-
In Stock
5+
No Image
ICM ICM35599
WWII US Military Patrol (G7107 with MG M1919A4
Military vehicle kits (injection)
Catalogue
£49.80
£44.82
-
In Stock
5+
ICM ICM35517
Soviet ZiL-131 KShM, Soviet Army Vehicle
Military vehicle kits (injection)
Catalogue
£35.80
-
In Stock
5+
Dragon DN6073
1:35
Schwere Spahwagen (heavy armoured railway wagon) with turret
Military vehicle kits (injection)
Catalogue
£44.99
-
In Stock
5+
No Image
MiniArt MT35337
Pz.Kpfw.IV Ausf.H NIBELUNGENWERK. MID PROD. HIGHLY DETAILED PLASTIC MODEL KIT WORKABLE TRACKS INCLUDED PHOTO-ETCHED PARTS INCLUDED CLEAR PLASTIC PARTS INCLUDED ALL HATCHES CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION METAL SURFACES AND WELD LINES ACCURATELY REPRESENTED IN THE MODEL DECAL SHEET FOR 4 OPTIONS
Military vehicle kits (injection)
Catalogue
£57.99
-
In Stock
5
No Image
Italeri IT6577
Panzerjager I tank destroyer NEW GLUEABLE RUBBER TRACKS - DECALS FOR 4 VERSIONS - COLOR INSTRUCTIONS SHEET - PHOTO-ETCHED FRET CONTAINS 1 FIGURE The self-propelled Panzerjager I tank destroyer was built on the hull of the Panzer I light tank and armed with an effective weapon of the period, the 47mm Czechoslovakian anti-tank gun. The Panzer I tank had in fact a protection and armament capability no longer in keep...
Military vehicle kits (injection)
Catalogue
£29.99
-
In Stock
14
Dragon DN6624
Pz.Kpfw.VI Ausf.E Tiger Sd.Kfz.181
Military vehicle kits (injection)
Catalogue
£79.99
-
In Stock
5+
MiniArt MT35279
1:35
M3A5 LEE HIGHLY DETAILED MODEL WORKABLE T41 TRACKS INCLUDED ALL HATCHES CAN BE POSED OPEN & CLOSED PHOTO-ETCHED PARTS INCLUDED CLEAR PARTS INCLUDED DECAL SHEET FOR 3 VARIANTS
Military vehicle kits (injection)
Catalogue
£47.99
-
In Stock
5
No Image
MiniArt MT37094
1:35
Soviet T-55A MOD. 1970 with INTERIOR KIT HIGHLY DETAILED MODEL DRIVER COMPARTMENT INTERIOR ACCURATELY REPRESENTED FULLY DETAILED FIGHTING COMPARTMENT INTERIOR HIGHLY DETAILED INTERIOR OF TURRET ALL HATCHES CAN BE POSED OPEN & CLOSED FULL AMMUNITION INCLUDED ENGINE AND TRANSMISSION INCLUDED INDIVIDUAL TRACKS INCLUDED CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 5 OPTIONS
Military vehicle kits (injection)
Catalogue
£77.99
-
In Stock
4
No Image
Takom TAK08007
Ersatz M7 2 in 1 German Sturmgeschutz/StuG.III Ausf.G self-propelled assault gun disguised as an American M7 'Priest' to infiltrate Allied positions during the Battle of the Bulge in the Ardennes in late 1944. Hatches can be fitted opened or closed, Link & Length tracks with track assembly jig, photo-etch parts. Two styles of engine cover. Markings for 150th Panzer Brigade (Kampfgruppe Y), Ardennes December 1944. Figures no...
Military vehicle kits (injection)
Catalogue
£44.99
-
In Stock
5+
Trumpeter TU05587
Soviet JS-1 Heavy Tank
Military vehicle kits (injection)
Catalogue
£63.99
-
Out of Stock
0
Dragon DN6313
Sherman Mk.III
Military vehicle kits (injection)
Catalogue
£59.99
-
In Stock
5+
No Image
IBG Models IBG35073
Pz.Kpfw. 7TP 731(p) - 7TP tank in German Service
Military vehicle kits (injection)
Catalogue
£34.99
-
In Stock
17
Hobby Boss HB84507
Lvkv 9040 Anti-Air Vehicle. In the middle of the '80s Hugglunds started the development of a new family of Combat Vehicles called Combat Vehicle 90 for the Swedish Army. Five prototypes were delivered in 1988 and were tested during extensive trials for three years. In March 1991, the Swedish Army placed the first series production order for the Swedish Combat Vehicle 90 Family. The first deliveries started in 1993, and now ove...
Military vehicle kits (injection)
Catalogue
£52.99
-
In Stock
3
Takom TAK02096
1:35
Pz.Kpfw.VI King Tiger Initial Production 4 in 1 German Heavy Tank Hatches can be fitted opened or closed aâé-¢ Workable tracks aâé-¢ Choice of four markings aâé-¢ PE parts.
Military vehicle kits (injection)
Catalogue
£69.99
-
In Stock
5+
MiniArt MT35287
AUSTRALIAN M3 LEE. WITH INTERIOR KIT HIGHLY DETAILED MODEL WORKABLE T41 TRACKS INCLUDED CONTINENTAL R975 ENGINE INCLUDED FULLY DETAILED FIGHTING COMPARTMENT INTERIOR DRIVER COMPARTMENT INTERIOR ACCURATELY REPRESENTED ALL HATCHES CAN BE POSED OPEN & CLOSED PHOTO-ETCHED PARTS INCLUDED CLEAR PARTS INCLUDED DECAL SHEET FOR 4 VARIANTS
Military vehicle kits (injection)
Catalogue
£67.99
-
In Stock
5+
No Image
ICM ICM35656
FWD Type B WWI US Ammunition Truck. The American company FWD (Four Wheel Drive Auto Company) was once an innovative enterprise that developed and produced four-wheel-drive vehicles. One of these cars, the 3-ton FWD Type B truck, had a classic hoodless layout with the driver's position above the engine compartment. The driver was located directly on the hood of the 4-cylinder engine with a capacity of 36 hp, the radiato...
Military vehicle kits (injection)
Catalogue
£34.99
£31.49
Out of Stock
0
No Image
Takom TAK02159
German Typ 205 Mƒ¤uschen concept Super Heavy Tank Little Mouse "Paper Panzer". Hatches can be fitted opened or closed, one-piece tracks, moveable suspension & road wheels. Choice of 4 markings.
Military vehicle kits (injection)
Catalogue
£57.99
-
In Stock
5+
Dragon DN3605
M1120 Terminal High Altitude Area Defence Missile Launcher BLACK LABEL Series
Military vehicle kits (injection)
Catalogue
£135.00
-
In Stock
5
Dragon DN6315
Back in stock! Pz.Kpfw.IV Ausf.FI (F)
Military vehicle kits (injection)
Catalogue
£69.99
-
In Stock
6
Dragon DN6608
Pz.Kpfw.VI Tiger I Initial Production
Military vehicle kits (injection)
Catalogue
£74.99
-
In Stock
5+

   1   |   2   |   3   |   4   |   5      »      [79]
| +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