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_manufacturer_id=362028 AND ps.id = 957
		 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 24 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
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 NM14403
1:144
'Vietnam': 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 NM14406
1:144
Tarmac and Taxiway: A single 1/144 scale sheet approx. A4 size
Diorama accessories (card)
Limited Availability
£5.75
-
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 NM14409
1:144
'WWII Luftwaffe Hardstand': 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 NM14411
1:144
Helipad: A new tarmac sheet depicting a military helicopter tarmac. This new state-of-the-art sheet depicts an actual modern military helicopter landing zone, and the terrain surroundings. It will fit various helicopter types and will therefore appeal to many enthusiasts. 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 NM14416
1:144
'Tarmac and Taxiway (No Lines)'. This a generic 1/144 scale sheet approx. A4 size. It complements your item #NM14406 but has the white lines removed, thus offering additional display options for the modeller.
Diorama accessories (card)
Limited Availability
£5.75
-
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 NM14418
'Battle of Britain Airfield Set V.1 (Brick Wall) with Bonus 3D Component': Three 1/144 sheets (approx. dimensions of base and backdrop: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch). Version 1 includes 3 sheets: The first sheet depicts the concrete surface upon which the fighter parks; the second is a backdrop sheet depicting a brick revetment with the airfield view and a Supermarine Spitfire Mk.I and a Hawker Hurrica...
Diorama accessories (card)
Limited Availability
£6.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

Noy's Miniatures NM14430
1:144
"Large South East Asia Dispersal": This cardstock print depicts a significantly larger concrete parking area for 1/144 U.S. Vietnam war aircraft. Approx. dimensions: Length: 20 cm (7.8 inch); Width: 14 cm (5.5 inch)
Diorama accessories (card)
Limited Availability
£5.20
-
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 NM14432
1:144
"Modern Fighter Hangar Set": Three 1/144 sheets as follows: Floor: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Rectangle Wall: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Square Wall: Length: 10.5 cm / 4.1 inch; Width: 10.5 cm / 4.1 inch. Quality printed on cardstock.
Diorama accessories (card)
Limited Availability
£12.50
-
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 NM14434
1:144
"Soviet Fighter Hangar Set": Three 1/144 sheets as follows: Floor: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Rectangle Wall: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Square Wall: Length: 10.5 cm / 4.1 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 NM14437
1:144
"IDF/AF HAS & Taxiway": A single 1/144 sheet as follows: Length: 21.5 cm / 8.47 inch; Width: 14.5 cm / 5.71 inch; Quality printed on cardstock
Diorama accessories (card)
Limited Availability
£5.20
-
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 NM14439
1:144
"H.M.S. Glory Deck Korean War": A single 1/144 sheet. Length: 14.8 cm / 5.83 inch; Width: 10.5 cm / 4.13 inch. Ideal for models of the Hawker Sea Fury and the Fairey Firefly, serving on this ship during the Korean War period. Quality printed on cardstock.
Diorama accessories (card)
Limited Availability
£4.20
-
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 NM14442
"2-in-1 WWII Luftwaffe Hangar (Inside) Set": Six 1/144 sheets as follows: Floor Component I: 9.3 cm / 3.7 inch x 13.2 cm / 5.2 inch; Floor Component II: 18.6 cm / 7.3 inch x 13.2 cm / 5.2 inch; Wall Component I: 13.2 cm / 5.2 inch x 5.7 cm / 2.2 inch; Wall Component II: 9.3 cm / 3.7 inch x 5.7 cm / 2.2 inch; Wall Component III: 9.3 cm / 3.7 inch x 5.7 cm / 2.2 inch; Accessory Detail Set: 7.4 cm / 2.9 inch x 5.3 cm / 2.1 ...
Diorama accessories (card)
Limited Availability
£20.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

Noy's Miniatures NM144K102
Israeli Air Force HAS (Hardened Aircraft Shelter) Noy's Miniatures have released a unique, very limited edition mixed-media kit in 1/144 scale, representing the typical HAS (Hardened Aircraft Shelter) in use for many years now by the Israeli Air Force. The kit includes 12 resin parts, 4 PE parts, a full color tarmac print and full color assembly instructions. It enables the modellers to build various sized HAS dioramas, accor...
Diorama accessories (mixed-media)
Limited Availability
£56.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 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 NM14441
1:144
"Large Soviet PAG-14 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
4


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