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 = 956
		 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 72 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
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
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
Takom TAKSP9002
DF-41 Ballistic Missile Launcher USUALLY é£54.99 TEMPORARILY SAVE 1/3RD!!! DongFeng (East Wind) Chinese road-mobile intercontinental ballistic missile (ICBM) on HTF5980A Transporter Erector Launcher (TEL). Detailed dual cabs and engine, alternative wheel hub options, photo-etch parts. Includes missile. Optional transport mode or launch mode. L: 292mm (approx), Missile diameter: 31mm (approx). Snowman Models boxing
Military vehicle kits (injection)
Special Offers
£35.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

UM-MT UMMT324
XT-26 chemical tank flame thrower WAS é£9.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£6.34
-
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

UM-MT UMMT608
Soviet OB-3 armored railway carriage with Soviet T-26 turret WAS é£13.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£8.80
-
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

UM-MT UMMT610
Armoured Locomotive OV of the type OB-3 WAS é£38.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£26.00
-
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

UM-MT UMMT611
The Armored train of type OB-3 'Railroader' WAS é£70.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£47.15
-
In Stock
2

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

UM-MT UMMT628
OB-3 bi-axle armourde car with 2 x Soviet T-26-1 conical turret WAS é£16.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£10.80
-
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

UM-MT UMMT639
Motorized armored railcar D-3 WAS é£16.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£11.07
-
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

UM-MT UMMT644
Armored train of the 27th-Division (1-'VICTORY', 2-'FOR THE MOTHERLAND') WAS é£106.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£70.82
-
In Stock
2

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

UM-MT UMMT645
1:72
ARMORED TRAIN 'ALEXANDR NEVSKY' WAS é£79.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£53.34
-
In Stock
2

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

UM-MT UMMT647
Armored train of the 29th-Division (1-'Krasnoyarec', 2-'YENISEY') WAS é£98.99 NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£66.02
-
In Stock
2

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

UM-MT UMMT648
1:72
Armored air defense platform of an armored train WAS é£26.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£18.00
-
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

UM-MT UMMT649
Experimental motorized armored car D-2 WAS é£16.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£11.07
-
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

UM-MT UMMT662
railway Armored car-carrier (DTR) WAS é£12.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£8.40
-
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

UM-MT UMMT663
DSH armored rail car WAS é£12.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£8.40
-
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

UM-MT UMMT665
Armored train STALINETS WAS é£98.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£66.02
-
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

UM-MT UMMT667
Armored car DTR-casemate on railway platform. available WAS é£19.70 NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£13.14
-
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

UM-MT UMMT673
MBV #01 motorized armored railcar WAS é£42.99. NOW BEING CLEARED!! SAVE 1/3RD!!! Length model, mm: 266 The set includes: 150 plastic parts photoetched scheme for painting detailed instructions in Ukrainian and English languages
Military vehicle kits (injection)
Special Offers
£28.67
-
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

UM-MT UMMT678
Armored train of type BP-43 "Soviet railroader" (#2, the 61st SATD). WAS é£92.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£61.89
-
In Stock
2

   1   |   2   |   3      »      [3]

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