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 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 19 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 NM14402
1:144
Modern IAF HAS/Hardened Aircraft Shelter Tarmac: A single 1/144 sheet (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.50 cm / 4.1 inch) WAS é£4.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Diorama accessories (card)
Special Offers
£2.80
-
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 NM14412
"WWI Airfield Set with Bonus Backdrop WAS é£8.70. LAST ONE BEING CLEARED!! SAVE 1/3RD!!! ": Three 1/144 sheets, including one FREE sheet (approx. dimensions of each sheet: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch)
Diorama accessories (card)
Special Offers
£5.80
-
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 NM14414
1:144
'South East Asia (SEA) Airfield set with Backdrop'. Two 1/144 sheets. WAS é£8.40. NOW BEING CLEARED!! SAVE 1/3RD!!! Approx. dimensions of each sheet: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch. A unique and innovative tarmac sheet set representing a U.S. airfield during the SEA conflict. This new state-of-the-art sheet set is aimed at the SEA U.S. aviation enthusiast and includes 2 sheets: The first sheet depicts th...
Diorama accessories (card)
Special Offers
£5.60
-
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 NM14419
1:144
'Battle of Britain Airfield Set V.2 (Grass Wall) with Bonus 3D Component': WAS é£6.99. TEMPORARILY SAVE 1/3RD!!! 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 2 includes 3 sheets: The first sheet depicts the concrete surface upon which the fighter parks; the second is a backdrop sheet depicting a grass revetment with the airfield view and a Supermar...
Diorama accessories (card)
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

Noy's Miniatures NM144201
1:144
"South East Asia (SEA) Helicopter Set". WAS é£7.99. LAST ONES BEING CLEARED!! SAVE 1/3RD!!! Two 1/144 sheets (approx. dimensions of base and backdrop: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch). The first sheet depicts the Marston mats (PSP) surface upon which the US military helicopter parked; the second is a backdrop sheet depicting a view of a typical US AFB in Vietnam with landscape elements, airplane and other...
Diorama accessories (card)
Special Offers
£5.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

Noy's Miniatures NM144202
1:144
"South East Asia (SEA) Helicopter Set for 3D". Two 1/144 sheets (approx. dimensions of base and backdrop: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch). This set is ready to receive 3D items (not included) to create an impressive 3D diorama (see below explanatory image). The first sheet depicts a ground surface to which a photoetched or resin PSP part may be added; the second is a modified backdrop sheet ready to rece...
Diorama accessories (card)
Special Offers
£5.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

Noy's Miniatures NM14421
1:144
"IDF/AF Airbase Set V.1 (Concrete Revetment) with Bonus 3D Component". Three 1/144 sheets WAS é£8.70. NOW BEING CLEARED!! SAVE 1/3RD!!! (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 concreteasphalt surface upon which the IAF jets roll; the second is a backdrop sheet depicting a concrete revetment with the airfield view ...
Diorama accessories (card)
Special Offers
£5.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

No Image
Noy's Miniatures NM14444
WWII Luftwaffe fighter hangar. WAS é£12.50. NOW BEING CLEARED!! SAVE 1/3RD!!! Item NM14444 "WWII Luftwaffe Fighter Hangar (Inside) Set": Three 1/144 sheets as follows: Floor Component: 13.2 cm / 5.2 inch x 9.3 cm / 3.7 inch; Wall Component I: 9.3 cm / 3.7 inch x 5.7 cm / 2.2 inch; Wall Component II: 13.2 cm / 5.2 inch x 5.7 cm / 2.2 inch. Quality printed on cardstock. The set includes 3 prints and depicts the inside area...
Diorama accessories (card)
Special Offers
£8.34
-
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
Noy's Miniatures NM14445
"IDF/AF Hardened Aircraft Shelter Set": WAS é£7.99. TEMPORARIOY SAVE 1/3RD!!! Two 1/144 sheets as follows: The set includes 2 prints (base + backdrop) and depicts the front area of an Israeli Air Force HAS (Hardened Aircraft Shelter). Base Component: 165 mm x 140 mm; Backdrop Component 140 mm x 110 mm. Quality printed on cardstock.
Diorama accessories (card)
Special Offers
£5.33
-
In Stock
7

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 NM32002
Modern IAF HAS/Hardened Aircraft Shelter Tarmac: A single 1/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). WAS é£32.40. LAST ONES BEING CLEARED!! SAVE 1/3RD!!!
Bases and cases
Special Offers
£21.61
-
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 NM32008
'Soviet PAG-14': A single 1/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). WAS é£32.40. LAST ONES BEING CLEARED!! SAVE 1/3RD!!!
Bases and cases
Special Offers
£21.61
-
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 NM32011
Helipad: A new tarmac sheet depicting a military helicopter tarmac. WAS é£32.40. NOW BEING CLEARED!! SAVE 1/3RD!!! 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/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch)
Bases and cases
Special Offers
£21.61
-
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 NM32020
1:32
"South East Asia (SEA) Helicopter Base". WAS é£32.40. LAST ONES BEING CLEARED!! SAVE 1/3RD!!! A single 1/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). Sheet depicts the Marston mats (PSP) surface upon which the U.S. military helicopter parked. Plotter-printed on heavy paper and shipped rolled in a sturdy carton tube.
Bases and cases
Special Offers
£21.61
-
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 NM32021
"2 in 1: South East Asia (SEA) Helicopter Set". WAS é£48.50. LAST ONES BEING CLEARED!! SAVE 1/3RD!!! Two 1/32 sheets (approx. dimensions of base and backdrop: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). The first sheet depicts the Marston mats (PSP) surface upon which the U.S. military helicopter parked; the second is a backdrop sheet depicting a view of a typical U.S. AFB in Vietnam with landscape elements, airp...
Bases and cases
Special Offers
£48.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 NM32022
1:32
"2 in 1: South East Asia (SEA) Helicopter Set for 3D". Two 1/32 sheets (approx. dimensions of base and backdrop: WAS é£37.50. NOW BEING CLEARED!! SAVE 1/3RD!!! Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). This set is ready to receive 3D items (not included) to create an impressive 3D diorama (see below explanatory image). The first sheet depicts a ground surface to which a photoetched or resin PSP part may be adde...
Bases and cases
Special Offers
£24.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

No Image
Noy's Miniatures NM32045
"IDF/AF Hardened Aircraft Shelter Set": Two 1/32 sheets as follows: WAS é£75.60. TEMPORARIOY SAVE 1/3RD!!! The set includes 2 prints (base + backdrop) and depicts the front area of an Israeli Air Force HAS (Hardened Aircraft Shelter). Base Component: 743 mm x 630 mm; Backdrop Component 420 mm x 330 mm. Plotter-printed on heavy paper (Not cardstock), this 1/32 set will be shipped rolled in a sturdy carton tube
Bases and cases
Special Offers
£49.98
-
In Stock
8

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 NM48033
"Large Soviet Hex Dispersal": This plotter-printed item depicts a significantly larger parking area for 1/48 Soviet & Warsaw Pact aircraft. WAS é£32.40. NOW BEING CLEARED!! SAVE 1/3RD!!! It comes inside a sturdy carton tube and is shipped like a poster. Approx. dimensions: Length: 59.4 cm (23.4 inch); Width: 42 cm (16.53 inch) (designed to be used with ICM kits)[Mikoyan MiG-25RBT]
Bases and cases
Special Offers
£21.61
-
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

Noy's Miniatures NM720202
1:72
"South East Asia (SEA) Helicopter Set for 3D". Two 1/72 sheets (approx. dimensions of base and backdrop: Length: 31 cm / 12.2 inch; Width: 22 cm / 8.66 inch). This set is ready to receive 3D items (not included) to create an impressive 3D diorama (see below explanatory image). The first sheet depicts a ground surface to which a photoetched or resin PSP part may be added; the second is a modified backdrop sheet ready to receive...
Diorama accessories (card)
Special Offers
£7.60
-
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 NM72028
"Soviet Bomber": WAS é£48.40. LAST ONE BEING CLEARED!! SAVE 1/3RD!!! 2 halves, separated along the demarcation lines, composing a single HUGE 1/72 sheet (approx. dimensions: Length: 1110 mm / 43.7 inch; Width: 780 mm / 30.71 inch). Shipped rolled in a sturdy carton tube. The print represents the complex elements that compose the concrete and asphalt surface upon which large Soviet bombers roll to and from the runway. Creat...
Bases and cases
Special Offers
£32.28
-
In Stock
3


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