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=8186
		 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 13 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 113
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

No Image
Roden ROD063
North-American AJ-1 Savage. WAS é£64.99. TEMPORARILY SAVE 1/3RD!!! The North-American AJ-1 Savage was a US carrier-borne bomber designed in the 1940s. Its main task was to carry and deliver a nuclear weapon, which at that time, due to its size, had to be deployed by aircraft near the target zone. Series production began in 1949 and continued until 1952 when a greatly improved AJ-2 version appeared. Due to frequent accidents du...
Aircraft kits (injection)
Special Offers
£43.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

No Image
Roden ROD341
Heinkel He-111H-6 WAS é£22.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£15.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

No Image
Roden ROD346
Heinkel He 111Z-1 Zwilling WAS 25.99. TEMPORARILY SAVE 1/3RD!!! Large German special purpose aircraft of the Second World War designed to tow heavy gliders. It was based on the He 111H front-line bomber, two of which were combined in one aircraft with a common wing centre-plane and an additional fifth engine to improve flight performance, primarily enabling the aircraft to haul heavy gliders. Twelve He 111Zs were built in 1941...
Aircraft kits (injection)
Special Offers
£17.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

No Image
Roden ROD348
1:144
Convair NB-36H Crusader. WAS é£74.99. TEMPORARILY SAVE 1/3RD!!! The Convair NB-36H Crusader was an American research aircraft built in 1955, based on the B-36H bomber to determine the possibility of installing a nuclear reactor and so remain airborne for a long time. The nuclear reactor was located in the rear of the bomber, and its operation was monitored via telemetry by specialists seated next to the crew in a special com...
Aircraft kits (injection)
Special Offers
£49.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
Roden ROD349
Boeing RC-135V/W USAF Rivet Joint Primary airborne signals intelligence aircraft WAS é£44.99. TEMPORARILY SAVE 1/3RD!! developed from the Boeing C-135 transport in the 1960s. The main tasks of the RC-135V and RC-135W are to identify sources of radio signals in the area of potential combat operations, collect information on land, sea and air targets, intercept radio communication between command posts and combat units, and mon...
Aircraft kits (injection)
Special Offers
£29.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

Roden ROD409
1:48
Cessna L-19/O-1 Bird Dog WAS é£49.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£33.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

No Image
Roden ROD454
1:48
Arado Ar-68F-1 USUALLY é£54.99. TEMPORARILY SAVE 1/3RD!!! The Arado Ar 68F-1 was a German multirole biplane developed in the mid-1930s, the main competitor to its contemporary the famous Heinkel He 51. At the time of its appearance, the aircraft displayed very good flight characteristics, but the rapid pace of technological progress and the introduction of monoplane fighters, such as the Bf-109, immediately pushed it like all ...
Aircraft kits (injection)
Special Offers
£36.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

No Image
Roden ROD631
Stearman PT-17 Kaydet USUALLY é£59.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£39.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
Roden ROD637
Sopwith 1é½ Strutter WWI Comic Fighter, 1917/18 WAS é£69.99. TEMPORARILY SAVE 1/3RD!!! Night fighter-interceptor converted from the standard two-seat 1é½ Strutter in late 1917. Modifications involved moving the pilot's seat to the rear of the aircraft and replacing the synchronized Vickers machine guns with Lewis guns on Foster mountings. This gave the pilot a better view but also made the aircraft more difficult to contr...
Aircraft kits (injection)
Special Offers
£46.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

No Image
Roden ROD815
1941 Packard Clipper WAS é£49.99. TEMPORARILY SAVE 1/3RD!!! The Packard Clipper was an American automobile first announced in 1941 by the Packard Motor Car Company, a leading manufacturer of luxury cars since the early 20th Century. By February 1942 some 16,000 '41 Clippers had been produced. When the United States entered WW2, Packard switched production to the war effort producing Packard Merlin aero engines, a licensed co...
Military vehicle kits (injection)
Special Offers
£33.33
-
In Stock
10+

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
Roden ROD817
1938 Ford V8-G81A Special WAS é£45.99. TEMPORARILY SAVE 1/3RD!!! German passenger car built at a subsidiary of the American Ford concern in Germany in the second half of the 1930s. Based on the Ford V8 car, production lasted from 1938 to 1941, during which time more than 8,000 of this model were manufactured. With the outbreak of World War II, many Ford V8-G81As were requisitioned from private owners and transferred to milit...
Military vehicle kits (injection)
Special Offers
£29.99
-
In Stock
10+

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
Roden ROD818
1:35
1941/42 Ford V8-G81A Funkwagen WAS é£59.99. TEMPORARILY SAVE 1/3RD!!! Specialized German communications vehicle converted from the G81A car. In order to install the sizeable radio equipment the car body was partly rebuilt and fitted with radio coils, cables and other dedicated equipment. These vehicles were used primarily in radio field units and sometimes in Wehrmacht reconnaissance units. Despite the relatively small numbe...
Military vehicle kits (injection)
Special Offers
£39.99
-
In Stock
10+

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
Roden ROD819
US GMC PD-3751 "Silverside Trailwagon" Trailways Company WAS é£79.99. TEMPORARILY SAVE 1/3RD!!! Intercity bus designed in the mid-1930s by Yellow Coach for one of the largest US coach companies, Greyhound Lines. The bus was a revolution in auto construction; corrugated aluminium and plastic materials were widely used in its build. Special attention was paid to maximum comfort for passengers. Following the construction of a w...
Military vehicle kits (injection)
Special Offers
£53.33
-
In Stock
10+


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