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

No Image
Caproni Ca.313/Ca.314 USUALLY é£29.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£19.99
-
In Stock
20+

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
1:72
Fokker F-27 Friendship SAR USUALLY é£36.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£24.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
Northrop F-5E Tiger II. SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET USUALLY é£29.99. TEMPORARILY SAVE 1/3RD!!! FIGURE NOT INCLUDED - CONTAINS 1 MODEL The Northrop F-5 was developed during the 1960s to meet the requirements of a number of NATO countries that needed a simple, cheap and easy to maintain multi-role fighter. Its airframe inspired the subsequent introduction of the F-5E Tiger II in the early 1970...
Aircraft kits (injection)
Special Offers
£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
Fairey Seal Foreign Service (3x camo) USUALLY é£56.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£37.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

Praga E-141 with tail skid/E-241 with tail wheel LIMITED!!! USUALLY é£22.99. NOW BEING CLEARED!! SAVE 1/3RD!!! In the mid-1930s, the Praga BH-41 was the follow-on aircraft.However, there were no engines for the construction of other aircraft, so the aircraft was modernized and the ZOD-260 diesel engine was used. The aircraft flew under the designation Praga E-141. 1. Praga E.141.1, Vojenskƒ© leteckƒ© uAili...itAº, ProstAº...
Aircraft kits (injection)
Special Offers
£15.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

McDonnell F-4B Phantom (1) 151015 VE/9 VMFA-115 Silver Eagles USUALLY é£11.99. NOW BEING CLEARED! SAVE 1/3RD!!!
Aircraft decals (military)
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

No Image
No.310 Sq. RAF - Hawker Hurricanes of Czechoslovak pilots USUALLY é£20.50. TEMPORARILY SAVE 1/3RD!!! 1. Hurricane Mk.I, P3143, F/L G.L. Sinclair/Sgt. B. Fƒ¼rst, September 1940 2. Hurricane Mk.I, R4089, F/L J. Jefferies, September 1940 3. Hurricane Mk.I, V6556, Sgt. E. Prchal, November 1940 4. Hurricane Mk.I, V6579, S/L G.D.M. Blackwood, winter 1940/1941 5. Hurricane Mk.I, P3889, P/O E. Fechtner, October 1940 6. H...
Aircraft decals (military)
Special Offers
£13.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

No Image
Soviet Missile stencils R-27ER/ET (AA-10 Alamo) & AKU-470 Stencils (Var.2) USUALLY é£13.99. NOW BEING CLEARED! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£9.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
XM158A1 2.75" Rocket Launcher USUALLY é£9.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£6.66
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
Mk-83 1000lb Bomb with Conical Fin. USUALLY é£14.99. NOW BEING CLEARED! SAVE 1/3RD!!! This set includes two (2) Mk-83 1000lb bombs, two (2) Conical fins and two (2) each of the following : M904 nose fuses, Mk-43 TDD and MXU-735 Nose Plugs. The Mk-83 was the weapon of choice for the USN and USMC during Operation Desert Storm and was used extensively throughout the war.
Aircraft detailing sets (resin)
Special Offers
£9.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
Mk-82 / BLU-111 500lb bomb with BSU-33 fin USUALLY é£19.99. NOW BEING CLEARED! SAVE 1/3RD!!! - Thermally Protected This set includes six Mk-82/BLU-111 bomb bodies with the thermal protective coating, six BSU-33 tail fins, and six each of the following : M904 nose fuzes, Mk-43 TDD, MXU-735 nose plugs. Accurately reproduced using personal measurements and photos of the real item. Parts come as seen in the photo, leaving mi...
Aircraft detailing sets (resin)
Special Offers
£13.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

Boeing F/A-18E Super Hornet Vinyl Mask Set (designed to be used with the Hasegawa kits) USUALLY é£6.99. NOW BEING CLEARED! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
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

American Fire Truck Crew (1910s) (2 figures) (100% new molds) USUALLY é£10.80. TEMPORARILY SAVE 1/3RD!!!
Figures (injection)
Special Offers
£7.20
-
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
1:32
Boeing F/A-18F Hornet early 3D-Printed & coloured Interior on decal paper (designed to be used with Trumpeter kits) USUALLY é£66.99. NOW BEING CLEARED! SAVE 1/3RD!!!
3D printed decal
Special Offers
£44.68
-
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
M-117R GP bombs with MAU-91 fin (6 pcs) (Republic F-105D/F-105G , General-Dynamics F-111, A-4 ,F-4, F-5, F-104, F-100, A-1 Skyraider, B-52, Canberra) (3D-Printed) (1/32) (designed to be used with Trumpeter kits) USUALLY é£16.90. TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£11.27
-
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
1:72
Northrop T-38C Talon ll wheels set (weighted) (designed to be used with Sword kits) USUALLY é£4.00. TEMPORARILY SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£2.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
Vought F-8E Crusader exhaust nozzle (designed to be used with Trumpeter kits) USUALLY é£24.40. TEMPORARILY SAVE 1/3RD!!!
Aircraft exhausts (resin)
Special Offers
£16.27
-
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
Douglas A-4E, A-4F, A-4K, A-4L, A-4M) Skyhawk exhaust nozzle (designed to be used with Hobby Boss kits) USUALLY é£8.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft exhausts (resin)
Special Offers
£5.87
-
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
1:48
General-Dynamics F-111D Cockpit with 3D decals (designed to be used with Academy kits) USUALLY 44.20. TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£29.47
-
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
General-Dynamics FB-111 Cockpit early modification with 3D decals (designed to be used with Academy kits) USUALLY 44.20. TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£29.47
-
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
General-Dynamics F-16A F110-GE closed exhaust nozzles (designed to be used with Academy kits) USUALLY é£7.20. TEMPORARILY SAVE 1/3RD!!!
Aircraft exhausts (resin)
Special Offers
£4.80
-
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

Curtiss SBC Scout x 6 sets per box USUALLY é£5.99. NOW BEING CLEARED! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£3.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

Hexagon Base USUALLY é£5.50. NOW BEING CLEARED! SAVE 1/3RD!!!
Bases and cases
Special Offers
£3.67
-
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:72
Daimler-Benz DB-603 WAS é£11.50. NOW BEING CLEARED!! SAVE 1/3RD!!! LAST ONE!!!
Aircraft engines and propellers (resin)
Special Offers
£7.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

Lewis Mk.III machine gun on Scarf ring mount WAS é£20.70. TEMPORARILY SAVE 1/3RD!!!
Aircraft guns (brass)
Special Offers
£13.80
-
In Stock
5+