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=8212 AND ps.id = 956 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 16 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
Special Armour SA72005
Sd.Kfz.250/1 Ausf.B (Neue Ausfƒ¼hrung). The Sd.Kfz.250 was developed to the Wehrmacht requirements to increase the numbers of German half track vehicle units. To speed up the development, the chassis of already existing non-armoured Demag D7 (Sd.Kfz.10) vehicle was used for which the Bussing company designed a new armoured hexagon-shaped hull, consisting of armoured plates joined at rather significant angles. The first prototy...
Military vehicle kits (injection)
Catalogue
£15.75
£14.18
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

Special Armour SA72017
Scheuch Schlepper The rocket-propelled Messerschmitt Me-163B tailless aircraft was not fitted with a usual undercarriage and so it lacked the ability to taxi to its take off position or after landing by its own power. And thus the Komet needed some other means to get around the airfield and it was found in the form of the Scheuch-Schlepper tractor. This rather diminutive vehicle was originally devised as a low-cost and widely ...
Military vehicle kits (injection)
Catalogue
£16.80
-
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

Special Armour SA72002
German Sd.Kfz.11 Leichter Zugkraftwagen 3t. The German Sd.Kfz.11 was the German Wermacht's most widely used unarmored half track. Many particular modifications were made for service use. This kit offers the basic version, an artillery gun-tractor. The kit contains two sprues with gray plastic parts and rubber track links. The decals offer three different camouflaged vehicles. A grey machine with roughly applied winter coat, a ...
Military vehicle kits (injection)
Catalogue
£15.30
-
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

Special Armour SA72014
A4/V-2 Rocket (black and white paint schemes) rocket with launch platform (USE WITH SA72001 AND SA72012)
Military vehicle kits (injection)
Catalogue
£14.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

Special Armour SA72001
SS-100/ST-100W Hanomag tow tractor. (USE WITH SA72003 AND SA720012)
Military vehicle kits (injection)
Catalogue
£15.30
-
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
Special Armour SA72026
15cm Nebelwerfer 41 'German Multiple Rocket Launcher' The Nebelwerfer 41 six-barrel rocket launcher was one of the German weapons the Allied soldiers were terrified of across all the battlefields it was used on. The German military introduced the Nebelwerfer into the service in 1941 and it remained used until the end of the war. Firing the smoke or explosive shells had also very strong psychologic effect mainly because of the ...
Military vehicle kits (injection)
Catalogue
£9.50
-
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
Special Armour SA72025
7,5 cm PaK 40 'German Anti-tank Gun'. The PaK 40 is arguably one of the most important German anti-tank weapons of the war. Our 1/72 model shows the gun in the wheeled version. Between 1939 and 1945 a total of more than 20,000 examples were built. They were used both with a wheeled chassis or mounted on a wide variety of Wehrmacht military vehicles. The gun was also exported to many German allies. It saw deployment on all fron...
Military vehicle kits (injection)
Catalogue
£9.50
-
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
Special Armour SA72024
3,7 cm PaK 36 'German Anti-tank Gun' In the initial stages of the war, the German Wehrmacht was equipped with 3,7cm PaK 36 anti-tank drawn guns. These were designed during the 1930s and first put to service in the Spanish Civil War. Before WW2, the guns were also exported to several countries including China, Finland or the Soviet Union. The latter also bought the licence rights and produced it as the M1930 (1-K) cannon. At th...
Military vehicle kits (injection)
Catalogue
£8.30
-
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
Special Armour SA72022
Sd.Ah 115 German tank transport trailer. This German Anhanger, or trailer, designated Sd.Ah.115 was widely used for transporting light weight tanks as well as other sorts of military vehicles of similar weights. The Wehrmacht had them in their service throughout the war.
Military vehicle kits (injection)
Catalogue
£14.20
-
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

Special Armour SA72004
Sd.Kfz.11/4 Nebelkraftwagen
Military vehicle kits (injection)
Catalogue
£15.30
-
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
Special Armour SA72020
Marder II (Sd.Kfz.131) 'German Tank Destroyer'. This Marder II is a really superb model of the German WW2 tank destroyer, the first of a couple of models moulding tools of we have recently acquired from MK72. Rightfully the model was awarded the Model of the Year prize at the time it was released first. The Marder II tank destroyer was developed using the Pz.Kpfw.II tank chassis. The Soviet, heavy armoured KV-1, KV-2 and mo...
Military vehicle kits (injection)
Catalogue
£15.30
-
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

Special Armour SA72003
Re-released! German A-4/V-2 Rocket (camouflage paint schemes) rocket with launch platform (USE WITH SA72001 AND SA72012)
Military vehicle kits (injection)
Catalogue
£15.30
-
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

Special Armour SA72018
Trailer for the 'Biber' German Midget Submarine. One of the almost 'miracle' weapons that were to stop the victorious advance of the Allies, which they fortunately did not, was also the German Biber midget submarine (designed to be used with Special Navy kits). In 1944, when the mass production of the sub was commenced, also a new type of a road trailer was needed to haul the sub to the area of its combat deployment and a vari...
Military vehicle kits (injection)
Catalogue
£14.20
-
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

Special Armour SA72015
1:72
A-Stoffanhanger liquid oxygen fuel tank on trailer for V-2 rockets. The A-stoffanhƒ¤nger is yet another model from our Special Armour range to represent a member of the A4/V2 family of support vehicles and the design of this model was completed using state of the art CAD. The parts of this kit are provided on two sprues of sand coloured plastic which are moulded within high quality injection steel moulds in order to make sure ...
Military vehicle kits (injection)
Catalogue
£15.30
-
In Stock
7

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

Special Armour SA72012
1:72
Meillerwagen A4/V-2 transporter (USE WITH SA72001 AND SA72003). We produce 1/72 scale high quality German A4/V2 rocket (kit nos. SA72003 and SA72014) and Hanomag SS-100 tractor (SA72001) in Special Armour line. SS-100 tractor's main purpose was not only rocket transport but it was also used on airfields for other heavy load transport. A4/V2 rockets were transported on low-bed trailers Vidalwagen (SA72010) and Meilerwagen. Meil...
Military vehicle kits (injection)
Catalogue
£17.80
-
In Stock
8

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

Special Armour SA72009
1:72
Vidawagen Street V-2 rocket transporter. The second kit from the Special Armour series offers the special trailer for transporting the A-4 (V-2 rocket "Revenge Weapon") rocket. The kit contains one sprue with plastic parts and a small fret with photo-etched parts. There are no decals, since the trailer carried no markings.
Military vehicle kits (injection)
Catalogue
£14.20
-
In Stock
6


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