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

Noy's Miniatures NM14404
1:144
'NATO': A single 1/144 sheet (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch).
Diorama accessories (card)
Limited Availability
£4.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

Noy's Miniatures NM14405
1:144
'Soviet Hex': A single 1/144 sheet (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch).
Diorama accessories (card)
Limited Availability
£4.20
-
In Stock
6

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

Noy's Miniatures NM14408
1:144
'Soviet PAG-14': A single 1/144 sheet (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch).
Diorama accessories (card)
Limited Availability
£4.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

Noy's Miniatures NM14422
"IDF/AF Airbase Set V.2 (Earth Revetment)". Two 1/144 sheets (approx. dimensions of base and backdrop: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch). Version 2 includes 2 sheets: The first sheet depicts the concrete\asphalt surface upon which the IAF jets roll; the second is a backdrop sheet depicting an earth & grass revetment with an airfield view and the Israeli desert mountains in the background, while a diamond f...
Diorama accessories (card)
Limited Availability
£8.70
-
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

Noy's Miniatures NM14425
"Winter WWII Luftwaffe Hardstand". A single 1/144 print. Approx. dimensions: Length: 310 mm (12.2 inch); Width: 220 mm (8.66 inch). Designed to take models of almost any WWII Luftwaffe machine, this state-of-the-art print authentically represents a typical combination of the wooden planks hardstand, the concrete surface and the snowy terrain seen all over Europe during the conflict. The print's size provides more than enough s...
Diorama accessories (card)
Limited Availability
£5.75
-
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

Noy's Miniatures NM14433
1:144
"Large Soviet Hex Dispersal": This cardstock print depicts a significantly larger parking area for 1/144 Soviet & Warsaw Pact aircraft. Approx. dimensions: Length: 20 cm (7.8 inch); Width: 14 cm (5.5 inch)
Diorama accessories (card)
Limited Availability
£5.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

Noy's Miniatures NM14436
"NATO HAS Set": Three 1/144 sheets as follows: Base Component I: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Base Component II: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Background: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch. Quality printed on cardstock.
Diorama accessories (card)
Limited Availability
£12.50
-
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

Noy's Miniatures NM14438
1:144
"PSP Marston Mat": A single cardstock print in 1/144 scale (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch).
Diorama accessories (card)
Limited Availability
£4.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

Noy's Miniatures NM14440
1:144
"Soviet Bomber" tarmac. The print represents the complex elements that compose the concrete and asphalt surface upon which large Soviet bombers roll to and from the runway. Created according to actual reference, this unique item authentically captures the wealth of tones and shapes that characterize the real thing. This large print will easily take any 1/144 bomber model, or several 1/144 fighter models. Less meticulous enthus...
Diorama accessories (card)
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

No Image
Noy's Miniatures NM14443
"WWII Aircraft Hangar (Inside) Set": Three 1/144 sheets as follows: Floor component: 18.6 cm / 7.3 inch x 13.2 cm / 5.18 inch; Wall component I: 18.6 cm / 7.3 inch x 5.65 cm / 2.2 inch; Wall component II: 13.2 cm / 5.18 inch x 5.65 cm / 2.2 inch. Quality printed on cardstock.
Diorama accessories (card)
Limited Availability
£20.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

Noy's Miniatures NM32003
1:32
Vietnam aircraft concrete parking area of U.S. airbases in Vietnam. Suitable for any 1/32 model of USAF and Marines land-based aircraft of the Vietnam conflict. This print comes inside a sturdy carton tube and is shipped like a poster.
Bases and cases
Limited Availability
£32.40
-
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

Noy's Miniatures NM32005
1:32
'Soviet Hex': A single 1/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch).
Bases and cases
Limited Availability
£32.40
-
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

Noy's Miniatures NM32039
1:32
"H.M.S. Glory Deck Korean War": A single 1/32 sheet. Length: 59.4 cm / 23.38 inch; Width: 42 cm / 16.54 inch. Ideal for models of the Hawker Sea Fury and the Fairey Firefly, serving on this ship during the Korean War period. Plotter-printed on heavy paper (not cardstock), this 1/32 sheet will be shipped rolled in a sturdy carton tube, just like a poster.
Bases and cases
Limited Availability
£32.40
-
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

Noy's Miniatures NM48010
1:48
'WWII Medium Bomber': A single 1/48 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch).
Bases and cases
Limited Availability
£32.40
-
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

Noy's Miniatures NM48020
1:48
"WWII US Pacific Heavy Bomber". A single 1/48 print. Approx. dimensions: Length: 890 mm (35 inch); Width: 600 mm (23.6 inch). Designed to take 1/48 scale models of US Pacific B-29s of WWII, as well as other bomber types serving in the US Pacific theater during that era, this large, state-of-the-art print authentically represents a B-29 dispersal. As with other Noy's Miniatures offerings, all 3D effects are printed. Item is plo...
Bases and cases
Limited Availability
£39.50
-
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

Noy's Miniatures NM48025
1:48
"Winter WWII Luftwaffe Hardstand". A single 1/48 print. Approx. dimensions: Length: 890 mm (35.04 inch); Width: 600 mm (23.62 inch). Designed to take models of almost any WWII Luftwaffe machine, this huge, state-of-the-art print authentically represents a typical combination of the wooden planks hardstand, the concrete surface and the snowy terrain seen all over Europe during the conflict. The huge size provides more than enou...
Bases and cases
Limited Availability
£39.50
-
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

Noy's Miniatures NM72027
1:72
Winter WWII Luftwaffe Hardstand area. Measures 396 mm x 280 mm and represents a smaller section of the Winter WWII Luftwaffe Hardstand area, though large enough to hold most aircraft as the larger version. Item is quality printed on cardstock.
Bases and cases
Limited Availability
£7.80
-
In Stock
6

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

Noy's Miniatures NM72031
1:72
"Fully Built Vietnam War Revetment Wall (7 Sections)".This hand-made cardstock item combines 2D (printed) and 3D elements to represent the famous revetment wall used by the US forces to protect its aircraft deployed in South East Asia during the conflict. Approx. dimensions: Length: 315 mm (12.4 inch); Width: 25 mm (0.98 inch); Height: 54 mm (2.13). The item is packed in a box (see photo) and it comes together with optional st...
Diorama accessories (card)
Limited Availability
£49.85
-
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

Squadron Canopies SQS9544
Seversky P-35 (designed to be used with Academy and Hobbycraft kits)
Aircraft canopies (vacform)
Limited Availability
£7.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

Squadron Canopies SQS9555
1:48
Mitsubishi A6M Zero (designed to be used with Fujimi, Hasegawa, Monogram and Tamiya kits) [A6M1 A6M2 A6M3 A6M5 A6M2b A6M2c Nakajima A6M2-N A6M6 A6M7 A6M8]
Aircraft canopies (vacform)
Limited Availability
£7.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

Squadron Canopies SQS9558
1:48
Heinkel He-162A-2 Salamander canopy x 2 (designed to be used with Dragon, Tamiya and Trimaster kits)
Aircraft canopies (vacform)
Limited Availability
£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

Squadron Canopies SQS9580
1:48
North-American P-51D Mustang x 2 (designed to be used with Tamiya kits)
Aircraft canopies (vacform)
Limited Availability
£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

Squadron Canopies SQS9583
Vought F4U-1/F4U-2 'Birdcage' x 2 (designed to be used with Tamiya kits)
Aircraft canopies (vacform)
Limited Availability
£7.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

Squadron Canopies SQS9622
1:48
Heinkel He-111H-4/He-111H-6 turrets (designed to be used with Monogram and Revell kits)
Aircraft canopies (vacform)
Limited Availability
£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

Squadron Canopies SQS9640
Gloster Gladiator Mk.I canopy (designed to be used with Eduard kits and Roden kits)
Aircraft canopies (vacform)
Limited Availability
£7.99
-
In Stock
5+

[1]   «      147   |   148   |   149   |   150   |   151      »      [269]
-100 -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