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 = 952 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 92 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
Italeri IT3622
VW Golf GTI First Series 1976/78. The Volkswagen Golf is an iconic vehicle designed and produced by the Wolfsburg car manufacturer. It was first introduced in Spring 1974 and immediately became a highly successful commercial product. The first series (1974-1983), offered leading edge technical capabilities to a very receptive consumer market of that period, including a water-cooled front engine, front-wheel drive, solid and ro...
Car kits (injection)
Catalogue
£36.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 IT3633
Mercedes 500 SLC CHROMED PARTS - RUBBER TYRES - COLOR INSTRUCTION SHEET During the 1960s Mercedes-Benz began development of a line of sports cars which were characterized by high performance and also great attention to aspects of motoring that would meet the requirements of the expanding "luxury" car market sector. The acronym SL (Sport and Leicht), identifies the various models that over the years have evolved and been pre...
Car kits (injection)
Catalogue
£35.99
£32.39
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 IT3631
Jaguar XJ 220 CHROMED PARTS - RUBBER TYRES - COLOR INSTRUCTIONS SHEET The Jaguar XJ 220 became famous for its top speed amongst series production cars, attaining 350 km / h, a record which was held from 1992 and 1994. The inception of the XJ220 is unusual insofar as it originated from the ideas and creativity of Jaguar employees. Some staff worked on innovative and original projects outside of the scope of routine commerc...
Car kits (injection)
Catalogue
£36.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 IT3959
MAN TGX 18.500 XXL Lion Pro Edition NEW PARTS - COLOR INSTRUCTION SHEET The MAN TGX XXL has been acclaimed by the commercial and industrial vehicle sector for both its stylishness and also for its "character", which provides a combination of driving comfort, reliability and profitability. An aerodynamic profile reduces fuel consumption and optimizes vehicle efficiency. The XXL cabin features maximum on-board comfort and ha...
Truck and trailer kits (injection)
Catalogue
£59.99
£53.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

Italeri IT0756
MAN 26.321 Formel Six
Truck and trailer kits (injection)
Catalogue
£59.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

No Image
Italeri IT3667
FIAT 131 Abarth Rally OLIO FIAT The Fiat 131 Abarth evolved from the initial two-door sedan model. The Abarth development involved many interventions such as the adoption of a lighter chassis, larger wheel mudguards, air intakes on the bonnet and on the sides to optimize the mechanical cooling system. The Fiat 131 Abarth used an in-line front longitudinal 4-cylinder engine with a light alloy head and 4 valves per cylinder. Ini...
Car kits (injection)
Catalogue
£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
Italeri IT3960
Opel Blitz Classic RUBBER TYRES - DECALS FOR 2 VERSIONS - COLORS INSTRUCTION SHEET The Opel Blitz project was set up with the aim of mass producing a robust and reliable commercial vehicle that could be used for different applications and tasks. Opel began production of the early trucks, based on a typically US "layout", in its Rƒ¼sselsheim plant in the early 1930s. This was a success story that evolved over time and contin...
Truck and trailer kits (injection)
Catalogue
£44.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

Italeri IT3783
Freightliner Heavy Dumper Truck RUBBER TYRES - CHROMED PARTS The renowned US Portland based company "Freightliner Trucks", which operates in the industrial vehicle sector and is owned by the automotive giant Daimler, is mainly known for being one of the key manufacturers of trucks used for "on road" transportation roles. However, it is also important to recognise the versatility and success over time, of those Freightliners...
Truck and trailer kits (injection)
Catalogue
£65.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 IT3625
1:24
Porsche RSR 934
Car kits (injection)
Catalogue
£30.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 IT3961
Scania S770 4x2 Normal roof SUPER DECAL SHEET FOR SPECIAL EDITION FROST - NEW PARTS - NEW FULLY DETAILED CABIN AND INTERIORS - FULLY DETAILED V8 ENGINE - OPENABLE DRIVER DOOR - OPENABLE RADIATOR GRILL - CROMED ADHESIVE - PHOTO ETCHED PARTS - RUBBER TYRES - COLOR INSTUCTION SHEET Scania S series a key player in the industrial and heavy commercial vehicles market has been designed to meet long-range logistics and transpo...
Truck and trailer kits (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
Italeri IT3626
BMW Gr.5 SUPER DECALS SHEET - COLORS INSTRUCTION SHEET In 1976 the FIA, within the scope of motor racing competition, introduced a new Group 5 section reserved for "special" production cars. The new rules imposed a body width similar to that of normal series production, upon which however, it was possible to apply dimensionally larger mudguards in order to benefit from wider track widths and larger tires. BMW participated i...
Car kits (injection)
Catalogue
£36.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 IT3684
Re-released! Lamborghini Countach 25th Anniversary
Car 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 IT3646
PORSCHE 944 S Cabrio COLOR INSTRUCTIONS SHEET The Porsche 944 was in production from 1982 to 1991 and was an evolution of the earlier 924 model, retaining both its characteristic styling and design features. The car was originally fitted with a fuel injected 2479cc engine capable of delivering 163 hp. and was manufactured in accordance with the Transaxle concept (rear engine and front shifter). The 944 was produced in seve...
Car kits (injection)
Catalogue
£29.99
£26.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

No Image
Italeri IT3665
Land Rover 109 LWB
Car kits (injection)
Catalogue
£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
Italeri IT3664
Ford Escort Zakspeed Gr.2
Car kits (injection)
Catalogue
£35.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 IT3927
Scania S730 Highline 4x2 DECAL SHEET FOR SPECIAL EDITION SCANIA V8 50th ANNIVERSARY - NEW MOULDS - NEW FULLY DETAILED CABIN AND INTERIORS - UPDATED ENGINE CYLINDER HEADS - OPENABLE DRIVER DOOR - OPENABLE RADIATOR GRILL - NEW SIDE SKIRTS, SPOILERS, TANKS AND EXHAUSTS - CROMED ADHESIVE - PHOTO ETCHED PARTS - RUBBER TYRES Scania S series " a key player in the industrial and heavy commercial vehicles market " has been designed ...
Truck and trailer kits (injection)
Catalogue
£84.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
Italeri IT3957
Volvo F-12 Intercooler(Low Roof) With Accessories
Truck and trailer kits (injection)
Catalogue
£59.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
Italeri IT3621
1:24
Fiat 131 Abarth 1977 Sanremo Rally Winner The Fiat 131 Abarth was built on the frame of the two-door Fiat sedan. The development made by Abarth considered several modifications and improvements starting from the reduction of the body weight using lighter materials. Then some new fenders, to permit the adoption of larger wheels, were installed and new air-intakes on the hood and on the sides to optimize the cooling of the mecha...
Car kits (injection)
Catalogue
£39.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
Italeri IT3635
Willys Jeep MB In 1940, after World War II had broken out, the US general staff ordered various factories to design a light multi-purpose vehicle with all-wheel drive. The American Bantam Company, Willys Overland, and Ford Motor Co. competed with each other.Willys prototype was chosen, which, after an initial run of 1500 vehicles, went into mass production at the beginning of 1941 and reached a total output of 640.000 units ou...
Car kits (injection)
Catalogue
£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
Italeri IT3926
1:24
IVECO Turbostar 190.48 Special NEW PARTS - SUPER DECALS SHEET FOR 2 VERSIONS - COLOR INSTRUCTIONS SHEET The Iveco Turbostar was an important commercial success for Italian manufacturer. Thanks to its characteristics of strength, reliability and performance, it achieved a significant position on the European truck market and it was the most widespread industrial vehicle in Italy in the late 80s.It was declined in numerous v...
Truck and trailer kits (injection)
Catalogue
£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

No Image
Italeri IT3623
1:24
Volvo 760 GLE During the 1980s, Volvo's 700 series was at the forefront of their auto production lines. First introduced in 1982, it was characterized by great attention to detail and a high level of finish, consistent with the "premium" positioning it strived for within both the European and US markets. The first model to be released was the Volvo 760. Available initially in a sedan configuration, it was noteworthy in design ...
Car kits (injection)
Catalogue
£36.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

No Image
Italeri IT3630
Toyota BJ-44 Land Cruiser Soft Top/Hard Top
Car kits (injection)
Catalogue
£36.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

No Image
Italeri IT3952
Scania R730 Streamline SUPER DECALS SHEET - SUPER CROMED ADHESIVE - ADDITIONAL CHROMED PARTS - NEW TYRES - NEW CHROMED WHEELS The Scania R series heavy tractor was developed on the current needs of the world market. SCANIA engines are built to carry out the most demanding jobs and highly appreciated as they are robust, reactive and efficient. The Scania R Series is designed to meet the highest demands in terms of fuel eco...
Truck and trailer kits (injection)
Catalogue
£74.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
Italeri IT3945
1:24
VOLVO F16 Globetrotter Canvas Truck with elevator SUPER DECALS SHEET - COLOR INSTRUCTION SHEET Introduced to the market in 1988, the Volvo F16 achieved considerable commercial success largely due to its innovative "powertrain" line which was able to achieve a high level of driving performance, low fuel consumption and low running and maintenance costs. The exceptional features of its 6-cylinder engine were complemented by t...
Truck and trailer kits (injection)
Catalogue
£69.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

Italeri IT3888
1:24
Shipping Container 20 Ft
Truck and trailer kits (injection)
Catalogue
£27.99
-
In Stock
2

   1   |   2   |   3   |   4      »      [4]

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