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 ps.id = 996
		 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 49 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

Trumpeter TU06243
Curtiss SBC Scout x 6 sets per box USUALLY é£5.99. NOW BEING CLEARED! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£3.99
-
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

Begemot BT350-002
Re-printed! French Navy Flags and Markings. USUALLY é£2.99. TEMPORARILY SAVE 1/3RD!!!
Ship decals
Special Offers
£1.99
-
In Stock
20+

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
AFV Club AF73517
USA Landing Vehicle Set 2 USUALLY é£21.99. TEMPORARILY SAVE 1/3RD!!! Korean War & WW2 New tooling. Set comprises 2-each GMC 353 Gas Tanker, M24 Chaffee, M19 Multiple GMC, M26 Pershing, M4 HST, M8 Light Armoured Car, M1 203mm Howitzer (transit), M1 203mm Howitzer (firing position), M1 155mm Long Tom (transit), M1 155mm Long Tom (firing position).
Military vehicle kits (injection)
Special Offers
£14.66
-
In Stock
17

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
AFV Club AFAG3557
M982-1A 'Excalibur' 155mm Artillery Shell (aluminium) USUALLY é£25.99. TEMPORARILY SAVE 1/3RD!!! Ammo used in IDF M109A2 'Rochev' & US M109A6 'Paladin' self-propelled howitzers
Military vehicle detailing sets (metal)
Special Offers
£17.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
Eduard Brassin ED653010
1:350
WWII USN battleship bollards 3D-Printed 1/350 USUALLY é£5.30. TEMPORARILY SAVE 1/3RD!!!
Ship detailing sets (resin)
Special Offers
£3.54
-
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
Eduard Brassin ED653013
Carley float for 40 people " Type 1 3D-Printed USUALLY é£5.30. TEMPORARILY SAVE 1/3RD!!!
Ship detailing sets (resin)
Special Offers
£3.54
-
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
I LOVE KIT LK65301
USS Yorktown CV-5 (Ex Merit) USUALLY é£179.99. TEMPORARILY SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£119.99
-
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

Master SM350-064
1:350
RN Roma Italian battleship armament - 381mm (9pcs), 152mm (12pcs), 90mm (12pcs) barrels (designed to be used with Trumpeter kits) USUALLY é£19.99. TEMPORARILY SAVE 1/3RD!!!
Ship detailing sets (metal)
Special Offers
£13.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
Master SM350-110
1:350
Schleswig-Holstein 1908 armament - 280mm (4pcs), 170mm (14pcs), 88mm (12pcs) barrels with resin mounts (designed to be used with Trumpeter kits) USUALLY é£16.80. TEMPORARILY SAVE 1/3RD!!!
Ship detailing sets (metal)
Special Offers
£11.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

No Image
Master SM350-111
German gun 8,8cm/35 (3.46in) SK L/35 - used in early 1900's (e.g. Schlezwig-Holstein 1908) - (resin, PE and turned parts) - (4pcs) (designed to be used with Trumpeter kits) Braunschweig Deutschland classes; Prinz Adalbert Scharnhorst USUALLY é£11.25. TEMPORARILY SAVE 1/3RD!!!
Ship detailing sets (metal)
Special Offers
£7.50
-
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
Trumpeter TU05362
H.M.S. Calcutta C-class Light Cruiser. USUALLY é£59.99. TEMPORARILY SAVE 1/3RD!!! H.M.S. Calcutta on July 9, 1918 at Vickers shipyard launched, modified for air defense cruiser in 1939, deployed in the Mediterranean. June 25, 1940, H.M.S. Calcutta in Bordeaux Coast joined the Canadian destroyer HMCS Fraser and HMCS Restigouche to Rescue 4000 the Germans besieged the French refugees. But because it was a large sea waves, resu...
Ship kits, full hull (injection)
Special Offers
£39.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
Zvezda ZVE9044
Russian Nuclear Icebreaker "Arktika" Project 22220 USUALLY é£89.99. TEMPORARILY SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£59.99
-
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
Revell RV5432
Gorch Fock WAS é£18.49. TEMPORARILY SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£12.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 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
Eduard ED53271
SMS Szent Istvan flags STEEL (designed to be used with Trumpeter kits) USUALLY é£9.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Ship detailing sets (etched)
Special Offers
£6.13
-
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

Bronco Models NB5046
Harbin Z-9WA Military Utility Helicopter (3 per box) WAS é£5.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£3.99
-
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

Hasegawa HAQG69
J.M.S.D.F. DDH Kaga Etched Detail Set (designed to be used with Hasegawa kits) WAS é£35.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Ship detailing sets (etched)
Special Offers
£23.99
-
In Stock
1

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
Trumpeter TU05357
Taszkient 1942 Russian Destroyer WAS é£51.99. TEMPORARILY SAVE 1/3RD!!! Italian-built "Tashkent" destroyer nicknamed "Blue Beauty". Built 1937. Provided humanitarian assistance and naval fire support for Soviet troops during the Sieges of Odessa and Sevastopol 1941ƒ¯é¿é½"1942. Sunk by the Luftwaffe on 2nd July 1942. Refloated in 1944 but the wreck was beyond economic repair and scrapped.
Ship kits (injection)
Special Offers
£34.60
-
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

Trumpeter TU06228
1:350
Kamov Ka-31 Helix x 6 sets per box WAS é£5.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£3.99
-
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

Trumpeter TU06242
Brewster F2A Buffalo x 6 sets per box WAS é£5.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£3.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

Trumpeter TU06249
U.S. Navy Vought OS2U 6 sets per box WAS é£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

Trumpeter TU06250
U.S. Navy SC-1 Seahawk 6 sets per box WAS é£5.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£3.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
Trumpeter TU06283
1:350
Boeing F4B WAS é£7.99. TEMPORARILY SAVE 1/3RD!!! Accessory pack of 12 aircraft for USS Langley CV-1/AV-3 6 sprues, 2 a/c on each sprue, photo-etch.
Aircraft kits (injection)
Special Offers
£5.33
-
In Stock
5

   1   |   2      »      [2]

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