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_category_id = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



Found 6,710 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

Scale

Product Type

Manufacturer

Added Within

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

A-Model AMU72330
Dassault Falcon 100
Aircraft kits (injection)
Limited Availability
£24.80
-
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

A-Model AMU72336
1:72
Dornier Do-J Wal flying boat Decals Russia
Aircraft kits (injection)
Limited Availability
£61.50
-
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

A-Model AMU72339
1:72
Dornier F Wal Dutch
Aircraft kits (injection)
Limited Availability
£61.50
-
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

A-Model AMU72342
1:72
Yakovlev Yak-42D 'Consulate Jet' Fuselage & wings white epoxy resin Engine, wheels , canopy , etc plastic 1 decal of markings Masks & photo etched parts.
Aircraft kits (injection and resin)
Limited Availability
£174.70
-
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
HGW HGW233917
Republic P-47D Thunderbolt Razorbacks OVER NEW GUINEA Pt.4 (1x HGW232917 + 2x HGW232921)
Aircraft decals (military)
Limited Availability
£24.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

AJM Models AJM350-002
1:350
H.M.S. Queen Of Thanet British armed paddle steamer WWII
Ship kits, full hull (resin)
Limited Availability
£91.20
-
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

AJM Models AJM350-003
1:350
H.M.S. Admiralty Type Scott class destroyer flotilla leader
Ship kits, full hull (resin)
Limited Availability
£113.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

AJM Models AJM350-004
1:350
HMAS Stuart, Australian Scott class destroyer flotilla leader
Ship kits, full hull (resin)
Limited Availability
£113.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

AJM Models AJM350-005
1:350
H.M.S. Malcolm
Ship kits, full hull (resin)
Limited Availability
£113.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

AJM Models AJM350-006
1:350
H.M.S. Douglas
Ship kits, full hull (resin)
Limited Availability
£113.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

AJM Models AJM350-009
1:350
H.M.S. Mckay after the last stage refit in 1945. with special Twin 6pdr 10cwt (57mm] adapted for use against e-boats and u-boats.
Ship kits, full hull (resin)
Limited Availability
£104.50
-
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

AJM Models AJM700-006
1:700
H.M.S. Douglas
Ship kits, waterline hull (resin)
Limited Availability
£47.50
-
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

AJM Models AJM700-024
1:700
H.M.S. Alynbank anti-aircraft ship
Ship kits, waterline hull (resin)
Limited Availability
£79.80
-
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

AJM Models AJM700-025
1:700
H.M.S. Springbank anti-aircraft ship
Ship kits, waterline hull (resin)
Limited Availability
£79.80
-
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

AJM Models AJM700-028
USS Monitor 1945 LSV-5 http://www.modelarstwo.koszalin.pl/index.php/porady/496-jak-zrobic-wode?fbclid=IwAR1n8diMgbnY5BSu-ppgjFto-5lDGNGnsTOhFHeaUb3R6i_KQ7B_kyWdsDQ
Ship kits, waterline hull (resin)
Limited Availability
£86.40
-
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
AJM Models AJM700-032
Bockenheim German Sperrbrecher 14
Ship kits, waterline hull (resin)
Limited Availability
£75.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

Anigrand Craftswork ANIG2048
1:72
Douglas XA2D-1 Skyshark. In June 1945, with the success of the high performance AD Skyraider, Navy asked Douglas to design a new version of the AD with turboprop power, and selected Allison to design the power-plant. Allison proposed to install a pair of nose mounted engines to drive a pair of contra-rotating propellers. In cruise flight, one of the engine and prop could be shut down to increase its flight range. In 1947, two ...
Aircraft kits (resin)
Limited Availability
£47.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

Anigrand Craftswork ANIG2063
1:72
Bell YAH-63 attack helicopter. In 1972, the U.S. Army requested proposals for a new Advanced Attack Helicopter (AAH) intended for the all-weather anti-armor role. Preliminary designs were submitted by Boeing, Bell, Hughes, Lockheed and Sikorsky, and in 1973 both Bell and Hughes were awarded contacts for two prototypes. Bell's Model 409 was designated YAH-63, and Hughes' Model 77 became the YAH-64. The YAH-63 was based largely ...
Aircraft kits (resin)
Limited Availability
£57.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

Anigrand Craftswork ANIG2065
1:72
Convair Atlas-A/-B/-D/X-11/X-12/XB-65/SM-65B/CGM-16D 1st ICBM. In October 1945, U.S. Army issued supersonic missile requirement to the industries which received many military missiles technical papers from Nazi Germany. In 1946, Convair submitted several proposals and was received funding for the MX-774 rocket study. In 1949, Soviet detonated its first nuclear weapon, U.S.A.F. gave highest priority to Convair to develop a more...
Space kits (resin)
Limited Availability
£57.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

Anigrand Craftswork ANIG2068
1:72
North-American XB-28. In 1939, the USAAF issued requirement for a new medium bomber which could operate at high-altitude to avoid attack from ground fire. Martin and North-American responded with the requirement, Martin submitted the Model 182 which was redesigned from the Martin B-26 Marauder, and North-American proposed NA-63 which was based from the North-American B-25 Mitchell. In 1940, North-American's design was chosen t...
Aircraft kits (resin)
Limited Availability
£57.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

Anigrand Craftswork ANIG2073
1:72
Myasishchev M-17 Stratosfera. In May 1960, when the Lockheed U-2 spy-plane was shot down over Ukraine, American started to use alternative unmanned balloon that prevailing winds would carry it across Soviet territory. The Soviet PVO (air defense forces) found this tread difficult to counter, and ordered Myasishchev to be head of a secret tasked with a high-altitude balloon destroyer aircraft. The result was the M-17 Mystic-A. ...
Aircraft kits (resin)
Limited Availability
£67.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

Anigrand Craftswork ANIG2077
1:72
Boeing X-20 Dyna Soar. In 1945, millions of German military documents were confiscated by USAAF. One of the concept was the Sanger aerospace bomber. During post-war years, Bell was selected for the study on space-craft, and named BoMi. In 1956, the Air Force channeled Bell's development to a reconnaissance, bombardment vehicle. A competition for a glider prototype was opened. In 1959, Boeing won the competition, and was contra...
Aircraft kits (resin)
Limited Availability
£47.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

Anigrand Craftswork ANIG2085
1:72
Lockheed AH-56A Cheyenne Attack helicopter to replace AH-1. In 1965, with the growing involvement in Vietnam, the U.S. Army requested proposals for an Advanced Aerial Fire Support System (AAFSS) program intended to establish a technologically advanced attack helicopter. Preliminary designs were submitted by Lockheed and Sikorsky. In 1966, Lockheed's design, AH-56 Cheyenne was selected as the winner. An engineering development ...
Aircraft kits (resin)
Limited Availability
£57.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

Anigrand Craftswork ANIG2086
1:72
Boeing YUH-61. In 1965, the U.S. Army launched the Utility Tactical Transport Aircraft System (UTTAS) program. The initial specification called for an aircraft able to carry a crew of three and eleven troops with the main goal of replacing the Bell UH-1 Huey. Requests for proposals were issued to the industries in 1972. All the designs proposed in response to the specification were required to use two G.E. turboshaft engines a...
Aircraft kits (resin)
Limited Availability
£57.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

Anigrand Craftswork ANIG2090
1:72
North-American XA2J-1 Super Savage. Most powerful savage bomber. In 1945, after the first atomic bomb was dropped successfully, the U.S. Navy launched a program for a carrier-based attack aircraft that would be capable of delivering a atomic bomb. The North-American AJ Savage won a Phase 1 competition, a turbo-prop powered aircraft being phase 2, and the phase 3 was a larger jet-powered attack aircraft. In 1947, the Navy reque...
Aircraft kits (resin)
Limited Availability
£97.99
-
In Stock
2

[1]   «      57   |   58   |   59   |   60   |   61      »      [269]
-50 -20 -10 | +10 +20 +50 +100

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