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 = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 2,274 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

1:72
Fieseler Fi-156C-3 'Storch' (2) Rumanian Air Force Red 7 or Black 6 RLM 70/71/65 (designed to be used with Academy, Heller and SMER kits) WAS é£5.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£3.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

No Image
ALLIED MINE DETECTION EQUIPMENT WAS é£13.75. TEMPORARILY SAVE 1/3RD!!! Kit Contains Models of Mine Detection Equipment. 4 Types of Allied Metal Detectors (Soviet, British/Polish and US), 2 Types of German Mines, Bags, Marking Flags. Decal Sheet is Included.
Diorama accessories (injection)
Special Offers
£9.17
-
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

1:48
Curtiss P-40E Kittyhawk/Warhawk (designed to be used with Otaki and Revell kits) WAS é£7.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft canopies (vacform)
Special Offers
£5.33
-
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

Supermarine Spitfire Mk.IX (designed to be used with Heller, Italeri and Ocidental kits) WAS é£7.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft canopies (vacform)
Special Offers
£5.33
-
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

Mil Mi-8/17 Hip wheels, weighted (designed to be used with Eastern Express kits)[Mi-8T] WAS é£4.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£3.20
-
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

1:144
Mil Mi-26 Halo wheels (designed to be used with Eastern Express kits) WAS é£4.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£3.33
-
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

Sikorsky CH-53D Sea Stallion wheels with weighted tires/tyres (designed to be used with Airfix, Bilek, Fujimi, Italeri and Revell kits) WAS é£9.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£6.54
-
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

1:48
Supermarine Spitfire Mk.IXe armament (designed to be used with Hasegawa kits)[mk.ixc] WAS é£13.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£9.13
-
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

Mitsubishi A6M2/A6M3/A6M5 Zero - Exterior set set (designed to be used with Hasegawa kits) Highly detailed resin set containing tail cone parts for the most famous WWII Imperial Japanese Navy fighter. The set contains tail wheels and strut with its mount, last fuselage bulkhead and tail cone. It can be built with tail cone removed. This set is intended for A6M2/ 3/ 5 of all subversions produced by Hasegawa. WAS é£6.30. NOW B...
Aircraft detailing sets (resin)
Special Offers
£4.20
-
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

Mikoyan MiG-29 wheels (designed to be used with Trumpeter kits) [MiG-29B/MIG-29UB/MiG-29M/MiG-29K Fulcrum] WAS é£11.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£7.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

2 x Sanders SCSG-5A smoke generators for Sk60 and many other types of a/c. WAS é£5.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£3.67
-
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

Messerschmitt Bf-109G-2 (canopy frame paint masks (inside and outside)) + insignia and markings masks + decals (designed to be used with Hasegawa kits) WAS é£9.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£6.67
-
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

Mil Mi-24V Hind E gear well (designed to be used with Hobby Boss kits) WAS é£6.15. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£4.10
-
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

Supermarine Spitfire Mk.IX control surfaces early (designed to be used with Airfix kits) WAS é£6.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£4.27
-
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

Mikoyan MiG-29K/MiG-29M 'Fulcrum' intake covers (B) (designed to be used with Trumpeter kits) WAS é£4.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£2.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

McDonnell F-101B Voodoo pitot tube (designed to be used with Monogram kits) WAS é£3.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£2.20
-
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

1:48
Cover for ALQ-99 RAM generator WAS é£3.40 NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£2.27
-
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

IAI C-2/C-7 Kfir pitot tube (designed to be used with Avant Garde kits) WAS é£3.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£2.33
-
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

U.S. Navy USV Unmanned aircraft (In Action Series) WAS é£11.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft books
Special Offers
£7.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

Focke-Wulf Fw-190A-8 SMOOTH TREAD WHEEL SET (designed be used with Dragon, Hasegawa, Italeri and Tamiya kits) WAS é£8.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£5.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

Railroad Wheels WAS é£15.99.TEMPORARILY SAVE 1/3RD!!!
Diorama accessories (injection)
Special Offers
£10.66
-
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
TEMPO A400 ATHLET 3-WHEEL DELIVERY TRUCK WAS é£42.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 5 OPTIONS DOORS CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION
Civilian vehicles kits (injection)
Special Offers
£28.66
-
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
EMPO A400 LIEFERWAGEN. VEGETABLE DELIVERY VAN WAS é£42.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 3 OPTIONS DOORS CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION
Civilian vehicles kits (injection)
Special Offers
£28.66
-
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
U.S. TOW TRUCK G506 WAS é£52.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED MODEL BOX CONTAIN UNASSEMBLED PLASTIC MODEL OF TRUCK WITH 3 TON SERVICE CRANE DOORS, CAR HOOD CAN BE POSED OPEN OR CLOSED ENGINE ACCURATELY REPRESENTED CLEAR PLASTIC PARTS INCLUDED PHOTOETCHED PARTS INCLUDED 4 DECAL OPTIONS
Civilian vehicles kits (injection)
Special Offers
£35.33
-
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
AUSTIN ARMOURED CAR 3rd SERIES: WAS é£52.99. TEMPORARILY SAVE 1/3RD!!! FREIKORPS SERVICE. INTERIOR KIT HIGHLY DETAILED PLASTIC MODEL KIT. BOX CONTAINS MODEL OF ARMORED CAR WITH FULL INTERIOR DRIVER COMPARTMENT AND INTERIOR OF TURRETS ACCURATELY REPRESENTED. HIGHLY DETAILED ENGINE COMPARTMENT ALL HATCHES CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION. CLEAR PLASTIC PARTS INCLUDED 4 MARKING OPTIONS PE PARTS ARE INCLUDED
Military vehicle kits (injection)
Special Offers
£35.33
-
In Stock
5+