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 = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



Found 6,710 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

Print Scale PSL72178
1:72
Sukhoi Su-17 Part 1 1. Su-17M4 "Batmaaaaan!" Unit: 1st AE, 20th GvAPIB, 125th ADIB, 16th VA. Serial: 05 Gross-Dolln airfield. East Germany, March 1992. 2. Su-17M3 Unit: 136th IBAP. Serial: 26. Kandahar, Afghanistan, Summer 1986. This regiment arrived to Afghanistan from Chirchik (Turkmenian Military District). After the repairing some aircraft of the regiment were without the markings, some have Red Stars without border. ...
Aircraft decals (military)
Limited Availability
£15.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

Print Scale PSL72232
1:72
Aichi D3A2 Model 22 (Aichi and Showa built aircraft)
Aircraft decals (military)
Limited Availability
£15.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

Print Scale PSL72306
1:72
Ilyushin Il-2/Il-2M3/Il-2T
Aircraft decals (military)
Limited Availability
£15.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

Print Scale PSL72365
1:72
Polikarpov U-2/Po-2 Part 1 1. Po-2VS. Unit: 9th GbNBAD. Serial: 28. Mission of Polish rebels supply. September 1944. 2. U-2VS. Unit: 1st AE, 45th GvNBAD. Serial: 7. Pilot - CO of 1st AE, 45th GvNBAD K,Michailenko. Single white stripe means 1st AE (two stripes 2nd AE, three - 3rd AE). 3.Po-2VS. Unit: 23rd GvNBAD. Serial: 1. 4. U-2. Serial: 80. Engelsk flying school. July 1932. 5. U-2VS (LNB). Unit: 242nd NBAD. Seri...
Aircraft decals (military)
Limited Availability
£15.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

Syhart Decal SY48057
1:48
General-Dynamics F-16A Falcon FA-50 'Three Fifty's Fifty' 50 Ans 350Sqn 1992
Aircraft decals (military)
Limited Availability
£10.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

Syhart Decal SY48066
1:48
Dassault-Dornier Alpha Jet E25 '1000000 heures de vol' 2008 AJeTS.
Aircraft decals (military)
Limited Availability
£15.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

Syhart Decal SY48076
1:48
McDonnell F-4F + RF-4E Phantom II '45Y WTD-61' 2002 + 'Last Klick' 1995
Aircraft decals (military)
Limited Availability
£12.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

Syhart Decal SY48094
Dassault-Mirage IVP né°23 AV "40ans FAS" 1964-2004
Aircraft decals (military)
Limited Availability
£11.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

Syhart Decal SY48096
Dassault-Mirage IVP né°31 BD "30ans FAS" 1964-1994
Aircraft decals (military)
Limited Availability
£11.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

Syhart Decal SY48911
1:48
Insignes Aeronavale Francaise / French Naval Aviation Badges
Aircraft decals (military)
Limited Availability
£8.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

Syhart Decal SY72081
1:72
General-Dynamics F-16AM Falcon FA-131 "Solo Display 2007-2008" Belgian AF
Aircraft decals (military)
Limited Availability
£9.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

Syhart Decal SY72082
1:72
General-Dynamics F-16AM Falcon FA-134 "Solo Display 2009" Belgian AF
Aircraft decals (military)
Limited Availability
£9.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

Techmod TM32061
1:32
Junkers Ju-88A-4
Aircraft decals (military)
Limited Availability
£11.20
-
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
Eduard Brassin ED648583
Messerschmitt Bf-109G-6 Eberspƒ¤cher acoustic pipes (designed to be used with Eduard kits) (disc Feb 23) (Eduard sold out April 2023 part 1)
Aircraft detailing sets (resin)
Limited Availability
£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
Eduard kits EDK4453
Avia B.534 IV. serie Super 44 edition kit of Czechoslovak fighter aircraft Avia B.534 IV. serie in 1/144 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: yes - resin parts: no
Aircraft kits (injection)
Limited Availability
£8.60
-
In Stock
17

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

Montex MXK32275
Junkers Ju-88A-4 (canopy frame paint masks (inside and outside)) + insignia and markings masks (designed to be used with Revell kits) WAS é£10.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft paint masks (self adhesive)
Limited Availability
£7.33
-
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
Rising Decals RD72097
64th Hiko Sentai (12x camo) Includes 12 camouflage schemes: Nakajima Ki-43-I, 2nd Chutai, Malaya 1942 Ki-43-I, 3rd Chutai, Fussa airbase, Autumn, 1941 Ki-43-II early production type, 1st Chutai Ki-43-II early production type, 1st Chutai Ki-43-II mid-production type (Ki-43-IIa), s/n5852, 1st Chutai Ki-43-II late production type (Ki-43-IIb), 3rd Chutai Ki-43-II final production type (Ki-43-II Kai), 1st Chutai, Sgt. Tos...
Aircraft decals (military)
Limited Availability
£12.20
-
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
Rising Decals RD72098
50th Hiko Sentai (11x camo) Ki-43-I, 1st Chutai, Sgt. Isamu Sasaki, 38-kill ace, Burma, 1942 Ki-43-I, 2nd Chutai, Sgt. Yukio Shimokawa, 16-kill ace Ki-43-I, s/n 389, 3rd Chutai, Tokorozawa airbase, June 1942 Ki-43-I, 1st Chutai Ki-43-II late production type (Ki-43-IIb), 3rd Chutai, Burma Ki-43-II s/n 5869 late production type (Ki-43-IIb), 3rd Chutai, Akyab airbase, Burma Ki-84 Ko, HQ Chutai, Maj. Koki Kawamoto, Phnom Pe...
Aircraft decals (military)
Limited Availability
£12.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

Eduard kits EDK8237
Mikoyan MiG-21PFM ProfiPACK
Aircraft kits (injection)
Limited Availability
£29.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

Kora KORC7276
Junkers Ju-86E-1 Luftwaffe Conversion set Part II. (designed to be used with Italeri kits)
Aircraft conversions (resin)
Limited Availability
£32.40
-
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

Q-M-T QMTE48002
1:48
R.B.F. British (flight tags in colour)
Aircraft detailing sets (etched)
Limited Availability
£5.75
-
In Stock
3

[1]   «      77   |   78   |   79   |   80   |   81      »      [269]
-50 -20 -10 | +10 +20 +50 +100

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