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 ps.id = 963 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 74 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
Meng Model MMRS-001
Ford GT40 Mk.II '66 (Pre-coloured Edition)
Car kits (injection)
Catalogue
£550.00
-
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
Meng Model MMRS-002
Ford GT40 Mk.II '66
Car kits (injection)
Catalogue
£299.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

Tamiya TA14016
1:12
Honda CX500 Turbo
Motorbike 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

Tamiya TA14065
1:12
GSX1100S Katana Custom tuned
Motorbike kits (injection)
Catalogue
£25.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 IT4712
Lancia Delta HF Integrale San Remo 1989 2 super decals sheets - Photo-etched fret and chromed parts - Black acetate mud splash guards - Accessories set with hoses, screws and springs - Front and rear opening bonnet - Opening front doors - Highly detailed interiors - Engine bay faithful reproduction - Working suspensions - Off-road rims and rubber tyres - Steering wheels - Colors instuction sheet The sporty version of the L...
Car kits (injection)
Catalogue
£199.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
Meng Model MMRS-004
McLaren MP4/4 1988
Car kits (injection)
Catalogue
£169.99
£152.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
Meng Model MMSPS-090
1:12
Formula Racing Driver 1988 (Resin)
Figures (resin)
Catalogue
£79.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

Tamiya TA12046
Team Lotus Type 72D 1972. The Lotus 72 was certainly an attention-grabbing car, and not just because of the stunning performance which swept Jochen Rindt to a world championship in 1970. An unmistakable wedge-shaped silhouette, cockpit-side radiators and torsion bar spring suspension were just some of the notable features on its Avant Garde design. Team Lotus were never ones to rest upon their laurels, and the 1972 iteration o...
Car kits (injection)
Catalogue
£110.00
-
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
Tamiya TA14141
Honda CBR 1000RR-R Fireblade Special Tamiya is pleased to announce that the Honda CBR1000RR FIREBLADE SP with special colouring to celebrate the 30th anniversary of the first generation "FIREBLADE" has been added to the 1:12 scale motorbike line-up. This kit is based on the 14138 Honda CBR1000RR FIREBLADE SP presented in 2020. The Honda CBR900RR FIREBLADE presented in 1992 impressed with its innovation, its exceptional power...
Motorbike 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 IT2990
1:12
General-Dynamics F-16 Cockpit 115 parts - Fully detailed real F-16 cockpit controls - Fully detailed MD ACES II ejection seat - Complete cockpit dials decals - Adhesive acetate silk seat belts - Pilot's visor, gunsight and radar scope clear parts - Pilot's helmet and oxygen mask The F-16 Fighting Falcon, produced by the American company General Dynamics since the late '70s, is a real icon of the military aviation history. I...
Miscellaneous
Catalogue
£32.99
£29.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
Italeri IT4711
McLaren MP4/2c Prost/Rosberg Detailed suspension - Highly detailed engine - Rubber tyres - Photoetched Parts - Removable body cowlings - 2 new decals sheet: Sheet A - Exterior, cockpit and engine. Sheet B - Composit materials - Decals for 2 versions: 1986 Campionship #1 Alain Prost - 1986 Campionship #2 Keke Rosberg The McLaren MP4/2 was one of the most successful racing cars, securing the most victories in the history of F...
Car kits (injection)
Catalogue
£139.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 IT4710
1:12
Bugatti Type 35 (New Tooling)
Car kits (injection)
Catalogue
£174.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
Hasegawa HABK12
Kawasaki KR250 (KR250A)
Motorbike kits (injection)
Catalogue
£49.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
Tamiya TA12054
Limited Edition Re-issue. Tyrrell 003 1971 Monaco
Car kits (injection)
Catalogue
£115.00
-
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
Tamiya TA14140
Ducati Superleggera V4
Motorbike kits (injection)
Catalogue
£49.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

Tamiya TA12036
Limited Edition Re-issue. Tyrell P34 6 wheeler
Car kits (injection)
Catalogue
£115.00
-
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
Meng Model MMRS-003
Ford GT40 Mk II '66 Champ (Pre-Coloured)
Car kits (injection)
Catalogue
£550.00
-
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

Tamiya TA12033
Renault RE-20 Turbo
Car kits (injection)
Catalogue
£109.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
Tamiya TA12051
Nissan Fairlady 240ZG Street-Custom Length: 356mm. Image shows assembled and painted kit. *Enlarged image Dressed to Impress Released in October 1969, the Nissan Fairlady Z went on to become a world-renowned high performance sports car. A stylish long nose, short deck body was married with a 2-liter inline-6 engine and cushioned by 4-wheel independent strut suspension. The 240Z was an export version with 2.4-liter engine ...
Car kits (injection)
Catalogue
£139.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
Tamiya TA12691
Team Suzuki ECSTAR GSX-RR 20 F. Image shows painted and assembled set. Fantastic Front Fork ƒ¢Ël"aâé-¦Amp up the detail on your 1/12 scale Suzuki GSX-RR model with this comprehensive set featuring dedicated plastic and metal parts, photo-etched components, and carbon fiber effect decals. ƒ¢Ël"aâé-¦Features a total of 12 metal components, including black anodized parts and a pre-painted yellow rear damper spring. ƒ¢Ël"aâé-¦Photo-etc...
Motorbike detailing sets
Catalogue
£34.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
Tamiya TA14139
Team Suzuki ECSTAR GSX-RR 20 F A Worthy Champion 2020 was the a superb year for the GSX-RR in MotoGP racing, as the Suzuki works machine took the rider's championship. Bearing the ECSTAR name of Suzuki's own engine oil brand and adorned with a blue and silver color scheme reminiscent of the great Japanese brand's bikes from the 1960s, the GSX-RR and its riders Joan Mir and Alex Rins consistently scored points throughout the s...
Motorbike kits (injection)
Catalogue
£49.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
Polar Lights POL977
1:12
Haunted Manor: Flight of the Vampire from Round 2
Film, Science Fiction or TV related
Catalogue
£42.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

Tamiya TA14029
1:12
Suzuki RG2501
Motorbike kits (injection)
Catalogue
£29.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
Italeri IT4708
Alfa Romeo 8C 2300 Roadster 100th Anniversary with colour Instructions Sheet - Steering wheels - Realistic suspension - Highly detailed engine - Rubber tires - 320 plastic parts - 30 Chromed parts - Photo-etched parts - Opening cowling - Screws, tubes and nylon net The Alfa Romeo 8C can be considered as one of the most famous sports cars of the 1930s. The car's name derives from the engine adopted: an 8-cylinder in-line sup...
Car kits (injection)
Catalogue
£184.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
Tamiya TA14138
1:12
Honda CBR 1000 RR-R Fireblade Announced in 2019, the CBR1000RR-R FIREBLADE SP is a stunning triumph of motorcycle design with a highly circuit-oriented feel, yet providing an accessible ride to road-going owners. There is plenty of race technology borrowed from the RC213V MotoGP bike, a class-leading 217.5hp engine, and state of the art electronics that assist with many aspects of the bike including its suspension. It is adorn...
Motorbike kits (injection)
Catalogue
£42.99
-
In Stock
1

   1   |   2   |   3      »      [3]

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