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=362578
		 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 18 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
Takom TAK01013
Battleship Yamato Anchors USUALLY é£46.99 TEMPORARILY SAVE 1/3RD!!! 15ton main anchor & stern anchor of the Imperial Japanese Navy (IJN) capital ship YAMATO. Built at Kure shipyard, Hiroshima Japan and launched in 1940, YAMATO weighed over 70,000 tons and needed a massive anchor to hold her steady in rough seas. The 15 ton main anchor was over 5 metres in length and was mounted at the bow. A smaller 3 metre anchor was mounted...
Ship 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
Takom TAK01014
Wiesel Mk 20 USUALLY é£69.99 TEMPORARILY SAVE 1/3RD!!! German Army reconnaissance and fire support vehicle with 20mm cannon. Hatches can be fitted opened or closed, workable suspension, road wheels & tracks, choice of 4 markings, PE, clear parts and figure included.
Military vehicle kits (injection)
Special Offers
£46.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
Takom TAK01015
1:16
Focke-Achgelis Fa 330 USUALLY é£54.99 TEMPORARILY SAVE 1/3RD!!! Bachstelze (Wagtail) WWII rotor glider or kite carried 'flat-packed' on board certain German U-boats. With the U-boat on the surface, the Fa 330 was assembled on deck and fed out from the stern by cable. Under tow the glider lifted to approx 120m. From the aerial vantage point the pilot could report observations up to 25 nautical miles away. Sortie complete the g...
Aircraft kits (injection)
Special Offers
£36.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
Takom TAK02140
1:35
US M103A2 Heavy Tank c.1964 USUALLY é£62.99 TEMPORARILY SAVE 1/3RD!!! America's last heavy tank, the improved large-turreted tracked platform for the long-range 120mm cannon. USMC type M103A1 tanks were up-engined with matching uprated transmission and classified M103A2; parts are included in the kit for the characteristic raised rear engine deck. 208 type M103A2s were converted for USMC service until retirement in the early ...
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

No Image
Takom TAK02144
Battleship Yamato 3rd Year Type 60 Caliber 15.5cm Gun Turret WAS é£79.99. TEMPORARILY SAVE 1/3RD!!! Naval gun of the Imperial Japanese Navy Overall length 40cm. Parts for No 1 and No 4 gun turrets. Guns can be posed raised or lowered, or a combination of both. ~
Ship detailing sets (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
Takom TAK02148
M114 A1 CRV USUALLY é£39.99 TEMPORARILY SAVE 1/3RD!!! US Army Command and Reconnaissance Vehicle. Hatches can be fitted opened or closed, Link & Length tracks with track assembly jig, photo-etch parts. Choice of 4 markings. Figure not included.
Gun 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
Takom TAK02150
Flakpanzer V Kugelblitz (ficticious) USUALLY é£54.99 TEMPORARILY SAVE 1/3RD!!! "What if?" anti-aircraft tank combining an oscillating twin flak turret mounted on the Panzer V hull. Hatches can be fitted opened or closed, Link & Length tracks with track assembly jig, photo-etch parts. Choice of 4 markings. Figure not included.
Gun kits (injection)
Special Offers
£36.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
Takom TAK02152
V-1 Flying Bomb Launch Site WAS é£89.99. TEMPORARILY SAVE 1/3RD!! Fieseler Fi 103 winged Vengeance or Retaliation Weapon 1 on Hellmuth Walter Werke designed slotted-tube slingshot/catapult (Flƒ¼gelbombe, Vergeltungswaffe 1, "V-1", Schlitzrohr, Schleuder) developed in 1939 at Peenemƒ¼nde Army Research Centre on the north eastern tip of Germany. Numerous launch ramps were installed by the Nazis at strategic locations in the ...
Diorama accessories (injection)
Special Offers
£59.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
Takom TAK02165
MQ-8B Fire Scout 1+1 US Air Forces Rotary Unmanned Aerial Vehicle (RUAV) with 4-blade rotor & stub wings. WAS é£49.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£33.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 TAK02603
Boeing/Hughes AH-64E Attack Helicopter (Limited Edition) USUALLY é£99.99 TEMPORARILY SAVE 1/3RD!!! E' of the World US-built Apache Guardian helicopter in service with other overseas nations. Limited edition release. New tooling. Full cockpit & interior, detailed weapons load. Folding rotor blades. Markings for Korean Army, Indonesian Army, Qatar Emiri Air Force, Saudi Arabian National Guard. Photo-etch & 3D-Printed parts in...
Aircraft kits (injection)
Special Offers
£66.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
Takom TAK02604
1:35
AH Mk.1 Apache Longbow British Army Attack Helicopter WAS é£99.99. TEMPORARILY SAVE MORE THAN 1/3RD!!! Sixty-seven multi-mission helicopter gunships were ordered for the British Army Air Corps (AAC) in 1995. Based on the American Boeing AH-64D Apache Longbow twin-engine tandem-seat helicopter, the first eight were built by Boeing in the United States from 1998 to 2000 to a UK design developed by Westland Helicopters (now Agust...
Aircraft kits (injection)
Special Offers
£59.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
Takom TAK05013
MAZ-537G w/ChMZAP-5247G Semi-trailer Mid-production & T-54B Russian 8x8 Prime Mover with gooseneck semi-trailer and T-54B tank load USUALLY é£67.99 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£45.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 TAK06002
Zeppelin P Class Airship USUALLY é£49.99 TEMPORARILY SAVE 1/3RD!!! 22 German Zeppelin P class airships were constructed between 1915 and 1917. Used on bombing raids over the UK in World War I, and for reconnaissance in the North & Baltic Seas. Product developed jointly by Takom and Snowman Model. Plastic injection fuselage. Includes photo-etch control surface struts and gun emplacements. 2 transparent supports for diplay in...
Aircraft kits (injection)
Special Offers
£33.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 TAK06007
Andrea Doria/Caio Duilio Italian Navy Horizon Class Destroyer USUALLY é£79.99 TEMPORARILY SAVE 1/3RD!!! Common New Generation Frigate [Italy: Destroyer] Horizon Class anti-air warfare (AAW) vessel developed in the 1990s initially by France, Italy & UK. Built by Franco-Italian joint venture Horizon SAS, two vessels for the Italian Navy were commissioned in 2009. UK dropped out in 1999 and developed the Type 45 instead. Choice...
Ship kits, full hull (injection)
Special Offers
£53.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 TAKSP6001
1:350
DDG-1000 Zumwalt Class Destroyer. WAS é£84.99. TEMPORARILY SAVE 1/3RD!!! Class of three U.S. Navy guided missile destroyers featuring turbo-electric propulsion system, low radar detectability, vertical launch weapon systems, aviation detachment of 2 SH-60 Seahawk helicopters. Kit includes metal barrels, photo-etch and additional 3D-Printed parts. Advanced Gun System (AGS) open/closed option. MH-60R Seahawk helicopter & MQ-8 F...
Ship kits, full hull (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
Takom TAKSP7036
SMS Lƒ¼tzow 1916 (full hull) WAS é£44.99. TEMPORARILY SAVE 1/3RD!!! Derfflinger-class battlecruiser of the German Imperial Navy. Laid down on May 15 1912, launched on November 29 1913, commissioned on August 1915. SMS Lƒ¼tzow First saw action on April 24/25 1916 during the bombardment of Great Yarmouth and Lowestoft. Scuttled on June 1 1916 after sustaining heavy damage in the Battle of Jutland. Product developed jointly by ...
Ship kits, full hull (injection)
Special Offers
£29.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
Takom TAKSP7057
USS Southerland DD-743 Gearing Class Destroyer 1945 (full hull) USUALLY é£49.99 TEMPORARILY SAVE 1/3RD!!! US Navy destroyer laid down May 27 1944, launched October 5 1944, commissioned December 22 1944. Named after Rear Admiral William Henry Hudson Southerland (1852-1933). Product developed jointly by Takom and Snowman Model. Kit come with bonus 1/72 scale Mk 38 5"/38 Twin Gun Mount. Ship (full hull) L: 170mm.
Ship kits, full hull (injection)
Special Offers
£33.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+


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