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 = 956
		 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 10 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 NM72002
1:72
Modern IAF HAS/Hardened Air craft Shelter Tarmac. Dimensions: Length: 29.50 cm/11.61 inch; Width: 20.50 cm/8.07 inch.
Diorama accessories (card)
Limited Availability
£5.75
-
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 NM72012
1:72
'WWI Airfield Set with Bonus Backdrop': Three 1/72 sheets (approx. dimensions of each sheet: Length: 31 cm / 12.2 inch; Width: 22 cm / 8.66 inch).
Diorama accessories (card)
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

Noy's Miniatures NM72020
1:72
'WWII US Pacific Heavy Bomber', is a large print, the approx. dimensions of which are: Length: 593 mm (23.35 inch); Width: 400 mm (15.75 inch). Designed to take 1/72 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 pr...
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 NM720201
1:72
"South East Asia (SEA) Helicopter Set". Two 1/72 sheets (approx. dimensions of base and backdrop: Length: 31 cm / 12.2 inch; Width: 22 cm / 8.66 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 base infrastructure parts, barrels and sandbags wall, ...
Diorama accessories (card)
Limited Availability
£11.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 NM72022
1:72
"IDF/AF Airbase Set V.2 (Earth Revetment)". Two 1/72 sheets (approx. dimensions of base and backdrop: Length: 31 cm / 12.2 inch; Width: 22 cm / 8.66 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 form...
Diorama accessories (card)
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

Noy's Miniatures NM72034
1:72
"Soviet Fighter Hangar Set": Three 1/72 sheets as follows: Floor: Length: 31.0 cm / 12.2 inch; Width: 21.9 cm / 8.6 inch; Rectangle Wall: Length: 31.0 cm / 12.2 inch; Width: 21.9 cm / 8.6 inch; Square Wall: Length: 21.9 cm / 8.6 inch; Width: 21.9 cm / 8.6 inch. Quality printed on cardstock
Diorama accessories (card)
Limited Availability
£17.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 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

Noy's Miniatures NM72025
"Winter WWII Luftwaffe Hardstand". A single 1/72 print. Approx. dimensions: Length: 593 mm (23.35 inch); Width: 400 mm (15.75 inch). Designed to take models of almost any WWII Luftwaffe machine, this large, 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 very large size provides more th...
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 NM72021
1:72
"IDF/AF Airbase Set V.1 (Concrete Revetment) with Bonus 3D Component". Three 1/72 sheets (approx. dimensions of base and backdrop: Length: 31 cm / 12.2 inch; Width: 22 cm / 8.66 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 and an IAF F-16 taking off, afterburner a-blaze,...
Diorama accessories (card)
Limited Availability
£11.99
-
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