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_manufacturer_id=8329
		 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 24 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
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
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 HB80182
Fieseler Fi-156D-1 'Storch' USUALLY é£35.99. TEMPORARILY SAVE 1/3RD!!! In 1936, RLM issued requirements for liaison and artillery fire spotting aircraft for the Wehrmacht and Luftwaffe. This aircraft must have a Short Takeoff and Landing (STOL) performance. The Fi-156 entered small scale series production in 1937. When she is in flight, the landing gear hung down. This is giving aircraft the appearance of a very long-legged wi...
Aircraft kits (injection)
Special Offers
£23.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 HB80183
1:35
Fieseler Fi-156C-3 'Storch' on skis USUALLY é£35.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£23.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 HB80299
ZLIN Z-42M USUALLY é£7.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£5.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 HB81772
Boeing CH-47A Chinook WAS é£82.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£55.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 HB81773
1:48
Boeing CH-47D Chinook USUALLY é£82.99. TEMPORARILY SAVE 1/3RD!!! The CH-47 Chinook, the workhorse of the US Army, is manufactured by the Boeing Vertol company. The Chinook has served as the prime mover for the US Army and other military forces for decades. Its principal missions include transport of troops, artillery, ammunitions, fuel, water, barrier materials, supplies and equipment on the battlefield. Other missions include...
Aircraft kits (injection)
Special Offers
£55.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 HB81777
1:48
Hawker Hurricane Mk.I USUALLY é£34.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£23.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 HB82940
1:72
9A52-2 Smerch M Rocket Launcher (Russian) 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 HB82952
15U175 TEL of RS-12M1 Topol-M ICBM complex WAS é£116.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£77.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 HB83213
Douglas A-26B Invader WAS é£139.99. TEMPORARILY SAVE 1/3RD!!! "Invaders"(renamed the B-26 between 1948-1965) is a US-made multi-purpose twin-engine light bombing / attack aircraft In some conflicts of World War II and the Cold War can be seen in its presence. To 1969, when only a limited number of some of the highlymodified aircraft (re-named the A-26) are still in service. Combat proven, A-26 fighter flying fast, and is twic...
Aircraft kits (injection)
Special Offers
£93.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 HB83894
French R35 Tank with FCM Turret WAS é£39.99. TEMPORARILY SAVE 1/3RD!!! The Renault R35, was a French light infantry tank of the Second World War.Designed from 1933 and produced from 1936, the type was intended as a light infantry support tank, equipping autonomous tank battalions, that would be allocated to individual infantry divisions to assist them in executing offensive operations. To this end it was relatively well-armour...
Military vehicle kits (injection)
Special Offers
£26.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
Hobby Boss HB84534
Pz.Beib.Wg V Ausf.A Panther WAS é£60.99. TEMPORARILY SAVE 1/3RD!!! is the common name of a medium tank fielded by Nazi Germany in World War II that served from mid-1943 to the end of the European war in 1945. The Panther's excellent combination of firepower, mobility, and protection served as a benchmark for other nations' late war and immediate post-war tank designs, and it is frequently regarded as one of the best tank desig...
Military vehicle kits (injection)
Special Offers
£40.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
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 HB84547
IDF PUMA GEV USUALLY é£71.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£47.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 HB84557
Leopard C2 MEXAS with TWMP WAS é£62.99. TEMPORARILY SAVE 1/3RD!!! To enable the Canadian Leopard C2 MBTs to operate the dozer blade as well as the mine plough and mine roller systems, an Engineer Equipment Interface Modification Kit (EEJM) has been developed by Krauss-Maffei. This allows the tank to operate the dozer blade or one of two mine clearing systems, permitting operation with the driver's hatch closed down. The exteri...
Military vehicle kits (injection)
Special Offers
£41.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

Hobby Boss HB85511
Soviet KrAZ-260B Tractor with 5P85TE TEL S-300PMU. WAS é£119.99. NOW SAVE 1/3RD!!! Production of the KrAZ-260 began some time during the early 1980s when it replaced the earlier KrAZ-255B on the production lines at the Kremenchug Motor Vehicle Plant. However, the vehicle was not disclosed to the general public until 1985 when examples were displayed towing 152-mm2A36 nuclear-capable field guns during a Red Square parade - the ...
Military vehicle kits (injection)
Special Offers
£79.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 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

No Image
Hobby Boss HB87256
Douglas A-4M Skyhawk WAS é£32.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£21.99
-
In Stock
5+


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