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=3080
		 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 21 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
Italeri IT5623
M.T.M. "Barchino" with crew USUALLY é£26.99. TEMPORARILY SAVE 1/3RD!!! Following its operational successes during WW1, the Italian "Regia Marina" continued with further post- war developments into various underwater and surface assault vehicles. The Motoscafo da Turismo Modificato (M.T.M.) for example, was designed to ensure a covert and rapid approach to targets with a low risk of detection from enemy forces. Closing in on th...
Ship kits, full hull (injection)
Special Offers
£17.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
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

Mikro-Mir MM35-020
United States Navy Mk.18 Torpedo x 2 USUALLY é£26.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£12.94
-
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
Trumpeter TU06722
H.M.S. Monmouth F235 Type 23 Frigate USUALLY é£31.99. TEMPORARILY SAVE 1/3RD!!! Royal Navy Duke Class Type 23 Frigate, one of thirteen based in Portsmouth and Devonport. Armed with Mk 8 4.5in medium gun, Sea Ceptor guided missiles, Harpoon long-range anti-ship missiles and small calibre guns.
Ship kits, full hull (injection)
Special Offers
£21.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

Zvezda ZVE8515
Roman Trireme Ship USUALLY é£67.99. TEMPORARILY SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£45.34
-
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

Zvezda ZVE9018
Hansa Kogge Crusaders ship USUALLY é£48.99. TEMPORARILY SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£32.67
-
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

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 RV5218
German Research Vessel Meteor WAS é£39.99. TEMPORARILY SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£26.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
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
Revell RV5824
USS Enterprise WAS é£9.99. TEMPORARILY SAVE 1/3RD!!!
Ship kits, full hull (injection)
Special Offers
£6.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 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
Meng Model MMPS-008
RMS Titanic WAS é£49.99. TEMPORARILY SAVE 1/3RD!!!!
Ship kits, full hull (injection)
Special Offers
£33.33
-
In Stock
13

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 HASP420
J.M.S.D.F. DDG Atago 'Super Detail' WAS é£79.99. NOW BEING CLEARED!! SAVE 1/3RD!!! Kit Features: Parts: 156 Model Size: 372 x 52 mm Markings: J.M.S.D.F. Guided Missile Destroyer ATAGO Code: 177 Etching parts: Handrails etc Product Specifications; Missile escort ship Atago "177" equipped with Aegis system (designed to be used with Hasegawa kits)
Ship kits, full hull (injection)
Special Offers
£53.33
-
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

No Image
Trumpeter TU03715
German Scharnhorst Battleship WAS é£359.99. TEMPORARILY SAVE 1/3RD!!! 25 sprues, hull, superstructure and decks. L: 1175mm W: 150mm, Total parts 1600+
Ship kits, full hull (injection)
Special Offers
£239.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 TU06734
H.M.S. Cornwall WAS é£46.99. TEMPORARILY SAVE 1/3RD!!! WWII British Kent Class heavy cruiser laid down in October 1924, launched in March 1926 and completed in May 1928. Lost on April 5th 1942 in an aerial attack by Japanese dive bombers south of Ceylon. Heavy casualties.
Ship kits, full hull (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

Mikro-Mir MM350-35
1:350
Iskra (Spark) Pravda-class Soviet submarine (late version) WAS é£19.80. TEMPORARILY SAVE 1/3RD!!! length, mm: 257 The kit includes: 2 frames with details 1 decal (sticker) 1 photoethed scheme for painting detailed instructions in Ukrainian and English languages
Ship kits, full hull (injection)
Special Offers
£13.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

Bronco Models NB5012
1:350
Chinese 039G 'Sung' Class Attack Submarine WAS é£9.99. NOW BEING CLEARED!! HALF PRICE TO CLEAR!!!
Ship kits, full hull (injection)
Special Offers
£4.99
-
In Stock
20+


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