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=363061 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 24 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
I LOVE KIT LK62007
1:200
USS Curtis Wilbur DDG-54 Guided Missile Destroyer 1994-present Fourth in the US Navy Arleigh Burke class of warships. Launched on May 16 1992 and commissioned on November 12 1993, it is named after Curtis D. Wilbur, the 43rd Secretary of the Navy. Single piece hull. Photo-etch parts, display stand & 1 x SH-60B included. L: 769mm, 111mm, Total parts 1300+
Ship kits, full hull (injection)
Catalogue
£172.99
£155.69
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

No Image
I LOVE KIT LK63538
US M1279 Utility (JLTV-UTL) c.2019"present Variant of the Joint Light Tactical Vehicle (JLTV). 2-door pickup truck with rear cargo bed that can be configured to carry a variety of loads. Currently in service with the US Army and Marine Corps. 7 sprues, cab & tyres. L: 158.7mm, W: 92.4mm, Total parts 290+
Military vehicle kits (injection)
Catalogue
£69.99
£62.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

No Image
I LOVE KIT LK63539
US GMC DUKW-353 w/ WTCT-6 Trailer c.1942 Six-wheel-drive amphibious truck manufactured by General Motors in 1942. Used by the US Army during World War II and the Korean War. DUKW denotes amphibious truck, 2ƒšé½-ton, 6x6, cargo, rear-mounted winch. The DUKW was designed to transport troops and supplies over land and water. Powered by a GMC 270cu in (4.4l) gas engine rated at 91hp. The DUKW could also tow an amphibious single-...
Military vehicle kits (injection)
Catalogue
£74.99
£67.49
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

No Image
I LOVE KIT LK63546
XM2001 Crusader SPH
Military vehicle kits (injection)
Catalogue
£79.99
£71.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

No Image
I LOVE KIT LK63519
US M3A5 Medium Tank The M3 Lee was an American medium tank used during World War II. In Britain, the tank was given two names based on its turret configuration and crew size. Tanks with US-pattern turrets were called "Lee" after Confederate general Robert E. Lee. Variants with British-pattern turrets were known as "Grant" after Union general Ulysses S. Grant. The diesel-engined M3A5 saw combat in North Africa, Italy and the ...
Military vehicle kits (injection)
Catalogue
£61.99
-
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

No Image
I LOVE KIT LK63528
NATO M1014 w/ M2042A1 GLCM TEL c.1979-91 Ground-Launched Cruise Missiles (GLCMs) were developed by the United States Air Force in the last decade of the Cold War to counter Soviet ballistic nuclear missiles deployed in Eastern Bloc European countries such as East Germany and Czechoslovakia, and in the Western Soviet Union. GLCMs were stationed in Europe as part of the NATO Double Track Decision (1979), initially at RAF Greenh...
Military vehicle kits (injection)
Catalogue
£139.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
I LOVE KIT LK63547
US M53 155mm Self-propelled Howitzer, 1950s-60s
Military vehicle kits (injection)
Catalogue
£72.99
-
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
I LOVE KIT LK63518
US M3A4 Medium Tank American WWII Lee/Grant medium tank with stretched welded hull and Chrysler multibank engine type A57. 20 sprues, upper & lower hull and turret. L: 176mm, W: 77.8mm, Total parts 640+
Military vehicle kits (injection)
Catalogue
£65.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
I LOVE KIT LK65704
1:700
USS North Carolina BB-55 Top Grade model kit. Detail-up set includes Wooden Deck, 800+ photo-etch parts, metal gun barrels & other metal parts. L: 317.5mm, W: 47.5mm, Total plastic parts 260+ The North Carolina (BB-55) was laid down 27 October 1937 by New York Naval Shipyard; launched 13 June 1940 and commissioned at New York 9 April 1941. North Carolina completed her shakedown in the Caribbean prior to the Pearl Harbor att...
Ship kits, full and waterline hull (injection)
Catalogue
£79.99
-
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
I LOVE KIT LK63535
1:35
M3 Medium Tank WWII British Army Grant with welded upper hull. 20 sprues, upper & lower hull and turret. Choice of 3 markings: Census T24656 (preserved in USA, ex-Budge Collection); 10th Armoured Division (UK); 8th Army (UK). L: 162.2mm, W: 77.8mm, Total parts 640+
Military vehicle kits (injection)
Catalogue
£64.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
I LOVE KIT LK62801
Lockheed-Martin F-22A Raptor
Aircraft kits (injection)
Catalogue
£99.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
I LOVE KIT LK63523
German Rhm.-Borsig Waffentrager WWII German 128mm gun carrier constructed by Rheinmetall-Borsig AG, Berlin.
Military vehicle kits (injection)
Catalogue
£63.99
-
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

No Image
I LOVE KIT LK65703
H.M.S. Hood 1941 Includes Detail-up set includes Wooden Deck, 580+ photo-etch parts, metal parts. The third and last HOOD was the mighty battle cruiser, which was laid down in 1915 as a class of four, and finally completed until 1920. Certainly she was the most powerful warship of her day and her displacement of 41,200 tons made far and away the heaviest capital ship in the world for Twenty years. In September 1940, Hood sp...
Ship kits, full and waterline hull (injection)
Catalogue
£72.99
-
In Stock
14

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 LK66602
Detail up set (designed be used with Trumpeter kits) 1/200 Bismarck Includes wooden decking, 20 resin parts, 11 photo-etch sheets, 200+ metal parts.
Ship detailing sets (etched brass and wood)
Catalogue
£329.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
I LOVE KIT LK63502
M19 U.S. Army Tank Transporter Soft Top Cab (Ex Merit)
Military vehicle kits (injection)
Catalogue
£112.99
-
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
I LOVE KIT LK66601
Bismarck 1941 Detail-up Set For use with Trumpeter item TU05358 Includes Wooden Deck, 6 large frets of photo-etch parts, metal & resin parts.
Ship detailing sets (etched brass and wood)
Catalogue
£119.99
-
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
I LOVE KIT LK65302
USS Enterprise CV-6 (Ex Merit)
Ship kits, full hull (injection)
Catalogue
£179.99
-
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
I LOVE KIT LK62001
USS Hornet CV-8 (Ex Merit) ƒ¯é¿é½Giant Size over 1,239 mm Long ƒ¯é¿é½One Piece Injection Moulded Hull ƒ¯é¿é½Fully Detailed Hangar ƒ¯é¿é½Carrier Deck Moulded in Clear Plastic to display Hangar detail ƒ¯é¿é½5 x F4F-4 Wildcat Fighters ƒ¯é¿é½5 x SBD 3/4 Dauntless Dive Bombers ƒ¯é¿é½8 x Douglas TBD-1 'Devastator' Torpedo Bombers ƒ¯é¿é½16 x Doolittle Raid B-25B with Proper Nose art and Markings ƒ¯é¿é½Optional Build with Ear...
Ship kits, full hull (injection)
Catalogue
£349.99
-
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
I LOVE KIT LK63501
M19 U.S. Tank Army Tank Transporter M20 Diamond T980/981 & M9 Rogers 45-ton Trailer (Ex Merit)
Military vehicle kits (injection)
Catalogue
£112.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
I LOVE KIT LK63504
British X-Craft Submarine (Ex Merit) Length: 448.5mm Beam: 77.2mm 5 sprues, 130+ parts, photo-etch, display stand & name plate.
Ship kits, full hull (injection)
Catalogue
£51.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
I LOVE KIT LK65307
H.M.S. Ark Royal 1939 (Ex Merit)
Ship kits, full hull (injection)
Catalogue
£145.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
I LOVE KIT LK65701
German Bismarck Battleship (Ex Trumpeter kit but with wooden decks and photo-etch)
Ship kits, full hull (injection)
Catalogue
£72.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

I LOVE KIT MM64804
Gloster Gladiator Mk.II
Aircraft kits (injection)
Catalogue
£24.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

I LOVE KIT LK64803
Gloster Gladiator Mk.I
Aircraft kits (injection)
Catalogue
£24.99
-
In Stock
10+


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