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=8069
		 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

No Image
Italeri IT0106
Caproni Ca.313/Ca.314 USUALLY é£29.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£19.99
-
In Stock
20+

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
Italeri IT1455
1:72
Fokker F-27 Friendship SAR USUALLY é£36.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£24.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
Italeri IT1461
Macchi MB 339 WAS é£56.99 TEMPORARILY SAVE 1/3RD!!! Contains 3 models and decals for all 6 special colors P.A.N. 60th Anniversary Special Livery
Aircraft kits (injection)
Special Offers
£37.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
Italeri IT2809
Lockheed TR-1A/B [U-2S/TU-2S] SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTIONS SHEET WAS é£32.99. TEMPORARILY SAVE 1/3RD!! FIGURE NOT INCLUDED - CONTAINS ONE MODEL The Lockheed U-2 is one of the best known high-altitude reconnaissance aircraft in the history of aviation and an icon of the Cold War. The development of the aircraft was approved in the early 1950's with the first flight in 1955. The U-2 was characteri...
Aircraft kits (injection)
Special Offers
£21.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
Italeri IT2811
North-American FJ-2/3 Fury WAS é£29.99. TEMPORARILY SAVE 1/3RD!!! SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTIONS SHEET FIGURE NOT INCLUDED The development of the North-American FJ-2 Fury was the result of a request from the United States Navy and the United States Marine Corps for the provision of a carrier-capable version of the F-86 Sabre swept-wing fighter. The carrier-capable aircraft of the period still us...
Aircraft kits (injection)
Special Offers
£19.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
Italeri IT2817
Mikoyan MiG-27 Flogger D USUALLY é£36.99 TEMPORARILY SAVE 1/3RD!!! ORIGINAL MOULDS FROM ESCI - SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTIONS SHEET FIGURE NOT INCLUDED The variable-sweep fighter-bomber Mikoyan-Gurevich Mig-27 (NATO designation Flogger D), was developed by the Soviet aviation industry from the airframe of the Mig-23 fighter to make it more operationally effective, in both ground attack and close s...
Aircraft kits (injection)
Special Offers
£24.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
Italeri IT2827
Northrop F-5E Tiger II. SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET USUALLY é£29.99. TEMPORARILY SAVE 1/3RD!!! FIGURE NOT INCLUDED - CONTAINS 1 MODEL The Northrop F-5 was developed during the 1960s to meet the requirements of a number of NATO countries that needed a simple, cheap and easy to maintain multi-role fighter. Its airframe inspired the subsequent introduction of the F-5E Tiger II in the early 1970...
Aircraft kits (injection)
Special Offers
£19.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

Italeri IT3106
Unknown
Leonardo Da Vinci Mechanical Drum The Marvellous Machines WAS é£19.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Miscellaneous
Special Offers
£13.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

Italeri IT3112
Unknown
Leverage Crane WAS é£23.99 . NOW BEING CLEARED!! SAVE 1/3RD!!! The leverage crane is developed from Leonardo's sketchbook concepts, in this case for a heavy-lift crane system that uses a ratcheting system to rotate in only one direction while providing leverage to raise the load with a fraction of the force needed with a simple pulley.
Miscellaneous
Special Offers
£15.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
Italeri IT3663
1:24
Mercedes G230 Feuerwehr RUBBER TYRES - DECAL FOR 4 VERSIONS - ADDITIONAL EQUIPMENT PARTS - COLOR INSTRUCTIONS SHEET USUALLY é£34.99. TEMPORARILY SAVE 1/3RD!!! The Mercedes-Benz G-Class (G denotes 'Gelƒ¤ndewagen', or off-road vehicle) was developed by the German car manufacturer during the 1970's and was developed through a partnership between Daimler-Benz and the Austrian Steyr-Daimler-Puch company. The 'G' series cars were...
Car kits (injection)
Special Offers
£23.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
Italeri IT3666
Volkswagen Golf Mk.1 POLIZEI WAS é£35.99. TEMPORARILY SAVE 1/3RD!!!
Car kits (injection)
Special Offers
£23.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
Italeri IT3951
40' Container Trailer WAS é£57.99. TEMPORARILY SAVE 1/3RD!!!
Truck and trailer kits (injection)
Special Offers
£38.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
Italeri IT3956
Mercedes-Benz SK 1844LS V8 SUPER DECALS SHEET FOR 3 VERSIONS - COLOR INSTRUCTION SHEET WAS é£59.99. TEMPORARILY SAVE 1/3RD!!! The Mercedes-Benz SK (Schwere Klasse) truck series which denotes the heavy vehicles in the range, were produced in the late 1980s by the famous German automotive manufacturer. The SK series directly evolved from the earlier Mercedes NG line and incorporated cabin improvements made more suitable for ...
Truck and trailer 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

Italeri IT4707
Renault RE23 Turbo WAS é£174.90. TEMPORARILY SAVE 1/3RD!!!
Car kits (injection)
Special Offers
£116.67
-
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
Italeri IT4709
Lancia Delta HF Integrale USUALLY é£229.99. TEMPORARILY SAVE 1/3RD!!!
Car kits (injection)
Special Offers
£153.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
Italeri IT5623
M.T.M. "Barchino" with crew USUALLY é£26.99. TEMPORARILY SAVE 1/3RD!!! Following its operational successes during WW1, the Italian "Regia Marina" continued with further post- war developments into various underwater and surface assault vehicles. The Motoscafo da Turismo Modificato (M.T.M.) for example, was designed to ensure a covert and rapid approach to targets with a low risk of detection from enemy forces. Closing in on th...
Ship kits, full hull (injection)
Special Offers
£17.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

Italeri IT6189
Free French Infantry (WWII) WAS é£9.99. TEMPORARILY SAVE 1/3RD!!!
Diorama accessories (injection)
Special Offers
£6.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
Italeri IT6585
T-34/85 Korean War [Soviet] WAS é£41.99. TEMPORARILY SAVE 1/3RD!!! with METAL GUN BARREL - CANVAS TOWING ROPE - GLUeABLE RUBBER OR LINK-AND-LENGTH TRACKS - PHOTO ETCHED FRET - DECALS FOR 4 VERSIONS - COLORS INSTRUCTIONS SHEET FIGURE NOT INCLUDED The T-34 tank is certainly one of the most famous armoured vehicles of the Second World War and it was the mainstay of the Soviet Army during the conflict. The most important inn...
Military vehicle kits (injection)
Special Offers
£27.99
-
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

No Image
Italeri IT6586
Sherman M4A3E8 - Korean War WAS é£39.99. TEMPORARILY SAVE 1/3RD!!! NEW PARTS - GLUABLE RUBBER TRACKS - DECALS FOR 4 VERSIONS - COLORS INSTRUCTIONS SHEET The M4 Sherman medium tank was the primary tank used by the United States during World War II. It has been produced in a significant number of units ant it formed the backbone of United States armored forces during at those time. It has been produced in several variants an...
Military vehicle kits (injection)
Special Offers
£26.66
-
In Stock
8


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