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:35
Pz.Kpfw.IV Ausf.H zimmerit (designed to be used with Academy kits) WAS é£34.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£22.87
-
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 Scale
Needle Fine Standard Class WAS é£13.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Airbrushes, accessories and spares
Special Offers
£8.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 Scale
Spray Regulator (F) Student/ Profi WAS é£9.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Airbrushes, accessories and spares
Special Offers
£6.66
-
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 Scale
Head (F) Student/Profi WAS é£18.99. NOW BEING CLEARED!! SAVE MORE THAN 1/3RD!!!
Airbrushes, accessories and spares
Special Offers
£11.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 Scale
Head assembly (F) WAS é£26.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Airbrushes, accessories and spares
Special Offers
£17.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

1:32
BAe Hawk T.1/T.1A (2) XX343/2 Empire Test Pilots School (ETPS), Boscombe Down July 1987. WAS é£7.99. TEMPORARILY SAVE 1/3RD!!! Raspberry Ripple scheme with red fin and rudder and rear fuselage with white front fuselage, Oxford blue undersurfaces, light aircraft grey inner wings top, red wing tips. XX343/2 Revised Raspberry Ripple scheme with white band full length of fuselage. XX184 70th Anniversary of equipping 19 Sqn with ...
Aircraft decals (military)
Special Offers
£5.33
-
In Stock
48

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:32
BAe Hawk T.1A XX285 100 Sqn 90th Anniversary 2007 with Yellow and Blue trim. WAS é£9.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£6.66
-
In Stock
50++

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:32
BAe Hawk T.1A (2) XX249 4th FTS 70th Anniversary 1921 Abu Sueir-Valley 1991, Red/White/Blue/Lt. Aircraft Grey camouflage; XX312/CF 100 Squadron two versions of Skull, Camouflage (Barley) Grey/m/s/grey Camouflage with Black fin and fuselage top and yellow/blue fuselage stripe. WAS é£7.99. SILLY PRICE!!! HALF PRICE!!! TEMPORARILY JUST é£3.99!!!
Aircraft decals (military)
Special Offers
£3.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

1:32
BAe Hawk T.1A (2) XX244 or XX245 2008(R) Squadron, 4 FTS RAF Valley 2011 Display Aircraft flown by Fl.Lt. Jules Fleming; XX226 4FTS RAF Valley 1993 Display Aircraft with Valley Dragon logo on fin. Double sheet. WAS é£10.50. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£6.99
-
In Stock
50++

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:200
Pacific V-Day figures (self adhesive) (designed to be used with Trumpeter kits)[USS Missouri BB-63] WAS é£97.85. NOW BEING CLEARED!! UNDER HALF PRICE!!! {Now out of production. Only available whilst Eduard have stock}
Ship detailing sets (etched)
Special Offers
£39.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

1:35
Soviet BMP-3 UAE (designed to be used with Trumpeter kits) WAS é£8.40. BEING CLEARED!! UNDER HALF PRICE!!!
Military vehicle paint masks (self adhesive)
Special Offers
£3.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

1:48
International Supermarine Spitfire Part 4 (4) Mk.Ia/Mk.1a N3029 DW-K 610 Sqn Biggin Hill 1940; Mk.IIb P8342 UZ-N 306 (Polish) Sqn 1941; Mk.IX MJ642 3W-11 332 Sqn Royal Netherlands Air Force 1951; (MH434) SM 41 Belgian Air Force WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

1:72
Escadrille 'La Fayette' (4) Spad S VII C1 No S1761 Capt W. Lovell 1917; Republic P-47D Thunderbolt 44-33390 9V-J 89 Missions; Dassault MD-450 Ouragan No 111 4-LH 1954; Dassault-Mirage 2000N No 313 1989
Aircraft decals (military)
Special Offers
£4.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

1:350
USN 14"/50 BARRELS (X12) WAS é£23.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Ship detailing sets (brass)
Special Offers
£15.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

1:32
"2 in 1: South East Asia (SEA) Helicopter Set for 3D". Two 1/32 sheets (approx. dimensions of base and backdrop: WAS é£37.50. NOW BEING CLEARED!! SAVE 1/3RD!!! Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). This set is ready to receive 3D items (not included) to create an impressive 3D diorama (see below explanatory image). The first sheet depicts a ground surface to which a photoetched or resin PSP part may be adde...
Bases and cases
Special Offers
£24.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
No Scale
Side Gravity feed and Suction feed Airbrush LAST ONES! NOW BEING CLEARED!! SAVE 1/3RD!!!
Airbrushes, accessories and spares
Special Offers
£16.66
-
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
No Scale
Single Action Suction feed Airbrush LAST ONES! NOW BEING CLEARED!! SAVE 1/3RD!!!
Airbrushes, accessories and spares
Special Offers
£7.33
-
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:72
de Havilland DH.60G Bata OK-ATR WAS é£43.20. NOW BEING CLEARED!! UNDER HALF PRICE!!!
Aircraft kits (resin)
Special Offers
£19.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

1:35
RSO PaK-40/4 75mm wheel masks (designed to be used with Dragon kits) WAS é£7.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle paint masks (self adhesive)
Special Offers
£4.80
-
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

1:48
Grumman A-6E/A-6E TRAM Intruder Canopy and Wheel Hub Mask set (designed to be used with Hobby Boss kits) WAS é£4.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Special Offers
£3.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

1:400
VOUGHT 156 F (x 5) WAS é£6.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (resin)
Special Offers
£4.66
-
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:48
Hazardous Materials (HAZMAT) Placards. WAS é£7.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.80
-
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

1:72
De Monge - Koolhoven M-101c2 parasol prototype Decals included WAS é£43.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (resin)
Special Offers
£26.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

1:32
to convert Mikoyan MiG-15 to MiG-15T tow aircraft with Tow Target KT-03 (designed to be used with Trumpeter kits) WAS é£111.30. BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£74.23
-
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

1:32
Aero L-39V + KT-04 Aero L-39V + KT-04 (designed to be used with HPH kits) WAS é£42.99. BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£28.67
-
In Stock
2