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
M1278 Heavy Guns Carrier - General Purpose (JLTV-GP) Joint Light Tactical Vehicle (JLTV). USUALLY é£75.99 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£50.66
-
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
NATO Individual Weapon Set A USUALLY é£22.99 TEMPORARILY SAVE 1/3RD!!! (A kit include 2 pcs of each weapon) (Including MP7 x 2, MK416 x 2, M16A4 x 2, M1014 x 2, M72A4 x 2, Stinger x 2, Javelin x 2, M141 x 2, AT4 x 2)"
Gun kits (injection)
Special Offers
£17.24
-
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
NATO Individual Weapon Set B USUALLY é£22.99 TEMPORARILY SAVE 1/3RD!!! (A kit include 2 pcs of each weapon) (Including M240 x 2, M249 x 2, M110 x 2, RGW90 x 2,Panzerfaust 3 x 2, Carl Gustaf M4 x 2, NLAW x 2, MK153 x 2)"
Gun kits (injection)
Special Offers
£17.24
-
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
Kawasaki Ki-60 USUALLY é£17.99 TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£11.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
Saab SK-37 Viggen Trainer USUALLY é£59.60 TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£39.74
-
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
Messerschmitt Bf-109E-1 'Legion Kondor' USUALLY é£13.99 TEMPORARILY SAVE 1/3RD!!! pre WWII Between 1939-40, the Messerschmitt Bf-109E was arguably the very best fighter aeroplane of the world. The type was produced in two main subvariants differing by the type of armament, either heavily or lightly armed. The lightly armed airframe was fitted with machine guns only, two of them above the engine, the other two in the wings. The...
Aircraft kits (injection)
Special Offers
£9.33
-
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
Stripes for Lozenge schemes. Each set contains two sheets with stripes in different colour combinations. USUALLY é£7.20 TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
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

No Image
Mil Mi-4A Hound USUALLY é£64.99. TEMPORARILY HALF PRICE!!!
Aircraft kits (injection)
Special Offers
£32.49
-
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
Lockheed P-38L Lightning (designed to be used with Tamiya kits)[P-38J] USUALLY é£6.80 TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (etched)
Special Offers
£4.53
-
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

Soviet T-34/76 Mod 1942 USUALLY é£6.25 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£4.17
-
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
German Marder III USUALLY é£6.25 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£4.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

No Image
1:35
AH Mk.1 Apache Longbow British Army Attack Helicopter WAS é£99.99. TEMPORARILY SAVE MORE THAN 1/3RD!!! Sixty-seven multi-mission helicopter gunships were ordered for the British Army Air Corps (AAC) in 1995. Based on the American Boeing AH-64D Apache Longbow twin-engine tandem-seat helicopter, the first eight were built by Boeing in the United States from 1998 to 2000 to a UK design developed by Westland Helicopters (now Agust...
Aircraft kits (injection)
Special Offers
£59.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
DF-41 Ballistic Missile Launcher USUALLY é£54.99 TEMPORARILY SAVE 1/3RD!!! DongFeng (East Wind) Chinese road-mobile intercontinental ballistic missile (ICBM) on HTF5980A Transporter Erector Launcher (TEL). Detailed dual cabs and engine, alternative wheel hub options, photo-etch parts. Includes missile. Optional transport mode or launch mode. L: 292mm (approx), Missile diameter: 31mm (approx). Snowman Models boxing
Military vehicle kits (injection)
Special Offers
£35.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
Mikoyan MiG-21PF AND MiG-21PFM STRIBRNE SIPY Limited edition kit of of Soviet Cold War jet fighter Mikoyan MiG-21PF AND MiG-21PFM in Czechoslovak Air Force in scale. DOUBLE KIT!!! WAS é£52.40. TEMPORARILY SAVE 1/3RD!!! - plastic parts: Eduard - marking options: 14 - decals: Eduard - PE parts: pre-painted - painting mask - resin part SPECIAL LOW PRICE! LIMITED! NO MORE WHEN SOLD! (Eduard sold out March 2024 part 1)
Aircraft kits (injection)
Special Offers
£34.93
-
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

Kuwaiti POW paintings 2003 WAS é£3.99. NOW BEING CLEARED! SILLY PRICE!!!
Diorama accessories (card and paper)
Special Offers
£0.89
-
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
Kyofu Japanese Navy Floatplane Fighter WAS é£7.50. TEMPORARILY SAVE 1/3RD!!! Includes 3 camouflage schemes: Kawanishi N1K1 "AI-105", 936th Kokutai, East Indies Detached Unit, 1945 N1K1 "YoG-101", s/n 514, 903rd Kokutai, Tateyama AB, Chiba prefecture, 1945 N1K1 "951-101", 951st Kokutai, Sasebo detachment, September 1945
Aircraft decals (military)
Special Offers
£4.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
Letov S-20 re-box, new decals WAS é£9.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£6.66
-
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
Hawker Tempest Mk.V 'Series 1' (not a Special Hobby kit) WAS é£13.70. TEMPORARILY HALF PRICE!!!
Aircraft kits (injection)
Special Offers
£6.85
-
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
Aero L-159A/E Alca 'Special Markings' re-box, new decals WAS é£19.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£13.33
-
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
Aero L-159A Alca 'Czech & Iraqi AF' re-box, new decals WAS é£19.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£13.33
-
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
AGO C.IV 'Late' new mould WAS é£19.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£13.33
-
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

XT-26 chemical tank flame thrower WAS é£9.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£6.34
-
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

Soviet OB-3 armored railway carriage with Soviet T-26 turret WAS é£13.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£8.80
-
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

Armoured Locomotive OV of the type OB-3 WAS é£38.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£26.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

The Armored train of type OB-3 'Railroader' WAS é£70.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£47.15
-
In Stock
2