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=8069 AND ps.id = 956 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 226 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

Italeri IT1403
Panavia Tornado IDS 60th anniversary 311é° GV RSV 'Special Colours'
Aircraft kits (injection)
Catalogue
£19.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
Italeri IT1471
1:72
AMX-T Ghibli two seat version The AMX "Ghibli" single-engine ground attack aircraft was the result of a collaborative project between the Italian and Brazilian aviation industries. The project was initiated in the 1980s to replace the dated Italian Air Forces Fiat G-91Y fighter-bomber. The Brazilian Air Force also needed to replace their Italian made MB-326s. The AMX had an aerodynamic lay-out characterized by a high swept ...
Aircraft kits (injection)
Catalogue
£29.99
£26.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
Italeri IT1412
Savoia-Marchetti SM.79 The S.M.79 Sparviero was created and manufactured in the 1930s by Italian company SIAI-Marchetti initially as a civilian aircraft, which then went on to achieve significant world speed records during this period. Featuring a typical three-engine low-wing configuration, it had a wood, canvas and metal frame. Subsequently reconfigured into military variants, it was widely used by the Italian Regia Aeronaut...
Aircraft kits (injection)
Catalogue
£27.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
Italeri IT6199
Battle of Normandy " Sainte Mere Eglise June 44. Within the overall strategic planning of the acclaimed operation "Overlord", the code name for the Allied landings on the beaches of Normandy, the French village of Sainte-Mere-ƒÆ'aâé-°glise played a key role. On battle plan maps it was rightly identified as an important road junction that the defending Germans could have used to carry out rapid counter attacks directed towards the...
Diorama accessories (injection)
Catalogue
£74.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

Italeri IT7508
Re-released! Autocannone RO3 with 90/53 AA Gun
Military vehicle kits (injection)
Catalogue
£16.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

Italeri IT7518
Re-released! M4A3 Sherman 75mm x 2 Fast Assembly Kits
Military vehicle kits (injection)
Catalogue
£16.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

Italeri IT7034
1:72
German Sd.Kfz.250/3
Military vehicle kits (injection)
Catalogue
£15.99
-
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
Italeri IT1470
Sepecat Jaguar T.2 COLOR INSTRUCTION SHEET - SUPER DECALS SHEET FOR 4 VERSIONS FIGURE NOT INCLUDED The Jaguar ground attack aircraft emerged in the 1960s from a collaborative project between the French and British aviation industries and was operated by the Royal Air Force until the late mid-2000s, thereby demonstrating extraordinary longevity and operational efficiency. Its two turbojet engines were able to attain and a...
Aircraft kits (injection)
Catalogue
£21.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
Italeri IT1460
AMX Ghibli Single Seater (New Tool) 100% NEW MOULDS - PHOTO-ETCHED PARTS - COLOR INSTRUCTION SHEET - SUPER DECALS SHEET FOR 4 VERSIONS FIGURE NOT INCLUDED - CONTAINS ONE MODEL The AMX "Ghibli" single-engine ground attack aircraft was the result of a collaborative project between the Italian and Brazilian aviation industries. The project was initiated in the 1980s to replace the dated Italian Air Force's Fiat G-91Y fighte...
Aircraft kits (injection)
Catalogue
£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
Italeri IT1464
Lockheed-Martin F-35A Lightning (Beast Mode) NEW PARTS - COLOR INSTRUCTION SHEET - SUPER DECALS SHEET FOR 5 VERSIONS FIGURE NOT INCLUDED The JSF (Joint Strike Fighter) program was established to develop a new fifth-generation all-weather multirole combat aircraft able to replace the United States and NATO strike-fighters. The aerospace industry team led by Lockheed Martin created the single-seat, single-engine, F-35 Ligh...
Aircraft kits (injection)
Catalogue
£32.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

Italeri IT7063
M3A1 White Scout Car
Military vehicle kits (injection)
Catalogue
£12.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

Italeri IT7003
M4A1 Sherman
Military vehicle kits (injection)
Catalogue
£12.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
Italeri IT1438
1:72
Fiat CR.32 Freccia SUPER DECALS SHEET FOR 5 VERSIONS - COLOR INSTUCTION SHEET FIGURE NOT INCLUDED Designed in the early part of the '30s, the Fiat CR 32 was powered by an V 12-cylinders, producing 600 hp. It was able to reach, as maximum speed, 375 Km / h. Among the most famous biplanes of aviation History the CR 32 was characterized by an extraordinary maneuverability. It was also strong, very reliable and well-armed fo...
Aircraft kits (injection)
Catalogue
£16.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
Italeri IT1468
Fairchil AC-119 Stinger SUPER DECALS SHEET FOR 4 VERSIONS - COLORS INSTUCTION SHEET The heavy transport-plane Fairchild C-119 served the US-Air Force as a basis for a conversion to a ƒšé«flying gunshipƒšé»,. This special construction was especially used in Vietnam for night missions against Vietcong supply lines and troop concentrations. The version AC-119 K was especially effective against truck due to its two 20mm guns an...
Aircraft kits (injection)
Catalogue
£44.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
Italeri IT1466
Junkers Ju-87G-2 Kanonenvogel. SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET FIGURE NOT INCLUDED - CONTAINS 1 MODEL The JU-87 dive bomber is certainly one of the best known aircraft of WW2. The characteristic "gull wing" aircraft gained great notoriety at the beginning of the war, and is considered, together with the Panzer Divisions of the Wehrmacht, as defining symbols of the Blitzkrieg. During the war, new...
Aircraft kits (injection)
Catalogue
£14.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
Italeri IT7079
Sd.Kfz.251/10 The Sd.Kfz.251 half-track was an armored fighting vehicle and was used by the Wehrmacht on all fronts, during the Second World War. Powered by a Maybach 6-cylinder petrol engine was able to achieve, even in off-road terrain, the speed of 55 km / h. Produced in huge numbers, it was mainly used to transport the "Panzergrenadiers" but, during the war, several versions has been developed to meet various operational n...
Military vehicle kits (injection)
Catalogue
£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

Italeri IT7511
M4A2 Sherman III includes 2 snap together vehicles
Military vehicle kits (injection)
Catalogue
£16.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

Italeri IT1236
BAe Sea Harrier FRS.1 Decals for 700A Flight, RNAS Yeovilton 1979 and Indian Navy 300 Squadron, INS Viktant 1982
Aircraft kits (injection)
Catalogue
£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

No Image
Italeri IT1441
Northrop F-5A Freedom Fighter. During the 1950s, the American Aircraft Company Northrop began developmental work into a simple and "low cost" fighter that would meet the needs of several NATO member air forces. The F-5 met the precise specification, it being a light single-seat fighter, small in size but extremely manoeuvrable and effective. It was relatively simple and cheap to produce and was operationally efficient. The ini...
Aircraft kits (injection)
Catalogue
£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

No Image
Italeri IT1423
North-American P-51A Mustang SUPER DECALS SHEET FOR 6 VERSIONS - COLOR INSTRUCTION SHEET FIGURE NOT INCLUDED - CONTAINS 1 MODEL The North-American P-51 Mustang is considered one of the most iconic aircraft in the history of the U.S.A.F. and widely credited as perhaps the best single-seat piston engine fighter of World War II. Among the earlier production versions, the P-51A was limited in its operational deployment to ju...
Aircraft kits (injection)
Catalogue
£14.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
Italeri IT1462
1:72
Short Stirling Mk.III NEW PARTS - INCLUDES A PATHFINDER VERSION WITH H2S RADAR - SUPER DECALS SHEET FOR 5 VERSIONS - PHOTO-ETCHED FRET - COLOR INSTRUCTIONS SHEET FIGURE NOT INCLUDED - CONTAINS 1 MODEL The Short Stirling was the first four-engined British heavy bomber of the Second World War. The design had high wing, four engines and the cabin elevated over the fuselage. The airframe was fully made in metal. Another feat...
Aircraft kits (injection)
Catalogue
£49.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
Italeri IT1463
1:72
Boeing V-22 Osprey The innovative Bell Boeing V-22 Osprey project was initiated during the 1980s, but only emerged into production and was deployed to various arms of the US armed forces, after enduring a long and complex developmental process which lasted for over twenty years. The Osprey is a tilt rotor, equipped with two tilting engines which have large three-blade propellers attached that make it able to land and take-off ...
Aircraft kits (injection)
Catalogue
£27.99
-
In Stock
4

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

Italeri IT7061
Sd.Kfz.124 'Wespe' 10.5cm Leichte Felhaubitze
Military vehicle kits (injection)
Catalogue
£17.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

Italeri IT0083
Boeing F/A-18E Super Hornet
Aircraft kits (injection)
Catalogue
£18.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

Italeri IT7006
1:72
Soviet T-62 Soviet main battle tank A successor to the T-55, the T-62 like its predecessor, was the backbone of Soviet and Warsaw Pact armored divisions for many years. Although very similar to the T-55, the new Soviet "Main Battle Tank" incorporated notable improvements in terms of its armor, aiming systems and above all with the main weapon system. The T-62 was in fact equipped with the new 115 mm high-speed smooth-bore gun....
Military vehicle kits (injection)
Catalogue
£15.99
-
In Stock
3

   1   |   2   |   3   |   4   |   5      »      [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