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=363071 AND ps.id = 953 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		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
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 142 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
Art Scale 200-D32030
Hawker Hurricane Mk.IIC part 4 - Albions Night Hunters Royal Air Force The decal sheet contain 3 markings: - Hawker Hurricane Mk.IIc, Grey LK-R, KR3353, 87. Squadron RAF, England 1941. - Hawker Hurricane Mk.IIc, Grey LK-?, HL864, 87. Squadron RAF, England 1941. - Hawker Hurricane Mk.IIc, Grey LK-Q, BN219, 87. Squadron RAF, Tangmere airfield, England 1942, pilot by Fl. Alex Thon.
Aircraft decals (military)
Catalogue
£11.20
Out of Stock
0

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
Art Scale 200-D32031
Hawker Hurricane Mk.IIb part 5 - USAAF cat. no. 200-D32031 The decal sheet contain 3 markings: - Hawker Hurricane Mk.IIb, black KZ492, unknown Squadron, Mediterranean area, 1944. - Hawker Hurricane Mk.IIb, yellow PB654, 350. Fighter Group, 346. Squadron, Emas airfield, Sardinia 1944. - Hawker Hurricane Mk.XII, JS327, 800. Squadron, HMS Biter, Torch Landings, Novenber 1942.
Aircraft decals (military)
Catalogue
£11.20
Out of Stock
0

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
Art Scale 200-D32032
Hawker Hurricane Mk.IIB / Mk.X part 6 - US Eagles The decal sheet contain 3 markings: - Hawker Hurricane Mk.IIb, unknown number, 350. Fighter Group, 346. Squadron, Emas airfield, Sardinia 1944. - Hawker Hurricane Mk.IIb, black HW805, unknown Squadron, Kunming airfield, China, 1943. - Hawker Sea Hurricane Mk.X, AM277, 891. Squadron, HMS Dasher, Torch Landings, November 1942.
Aircraft decals (military)
Catalogue
£11.20
Out of Stock
0

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
Art Scale 200-D32076
1:32
Mikoyan MiG-21F, MF Fishbed / CF-121 Redhawk program - Royal Canadian Air Force, 441. Squadron
Aircraft decals (military)
Catalogue
£19.99
£17.99
Out of Stock
0

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
Art Scale 200-D32077
Mikoyan MiG-21F-13 Fishbed part 2 - US Air Force TES (TAC) unit
Aircraft decals (military)
Catalogue
£11.60
£10.44
Out of Stock
0

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
Art Scale 200-D32078
Mikoyan MiG-21F-13 Fishbed part 4 - Izraeli Air Force, 101.Squadron, ASK no. 200-D32078 The decal sheet contain follow markings: - MiG-21F-13, no.007, the plane obtained by the defection of the Syrian pilot. The aircraft was standing on alert at Hazor with the ORA Mirages of 101. Squadron, Izrael, Hatzor AF Base, June 1967. - MiG-21F-13, no.007, shortly before the aircraft was handed over to the USA, Hatzor AF Base, Izrael,...
Aircraft decals (military)
Catalogue
£11.20
£10.08
Out of Stock
0

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
Art Scale 200-D32083
Douglas TBD-1 Devastator part 1 - Battle of Midway 4.June 1942, The decal sheet contain follow markings: Douglas TBD-1 Devastator, Bu.No. 0286,VT-3, pilot CAP W.G. Esders, Radio Operator/rear Gunner ARM2c B.Brazier, after shooting down Lt.Cdr. E.F. Lindsey Esders leads attack on aircraft carrier KAGA, the crew was killed in this action. Aircraft Carrier USS Yorktown. Douglas TBD-1 Devastator, Bu.No. 0279,VT-6, pilot Ens....
Aircraft decals (military)
Catalogue
£11.60
£10.44
Out of Stock
0

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
Art Scale 200-D32084
1:32
Douglas TBD-1 Devastator part 2 - Kwajalein and Wake Island Attack February 1942 The decal sheet contain follow markings: Douglas TBD-1 Devastator, Bu.No. 0294,VT-6, pilot Lt.R.E. Laub, Radio Operator/rear Gunner ARM1c W.C.Humphrey, crew took part in a bombing attack on the Wake Island. Aircraft Carrier USS Enterprise. Douglas TBD-1 Devastator, Bu.No. 0367,VT-6, pilot Ens.W.W Creamer, Radio Operator/rear Gunner RM3c W.F.G...
Aircraft decals (military)
Catalogue
£19.80
£17.82
Out of Stock
0

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
Art Scale 200-D32085
Douglas TBD-1 Devastator part 3 - pre-war period until 1941 The decal sheet contain follow markings: Douglas TBD-1 Devastator, Bu.No.0294, VT-3, 3-T-14, Aircraft Carrier USS Saratoga, NAS North Island, San Diego, California, January 1938. Douglas TBD1 Devastator, Bu.No.0299, VS-71, 71-S-16, pilot Lt.B.V.Clark, Radio Operator/rear Gunner H.G.Bradshaw, Aircarft Carrier USS Wasp, Atlantic, 23.05.1941. Douglas TBD-1 De...
Aircraft decals (military)
Catalogue
£16.20
£14.58
Out of Stock
0

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
Art Scale 200-D32086
Douglas TBD-1 Devastator part 4 - pre-war period 1940 The decal sheet contain follow markings: Douglas TBD-1 Devastator, Bu.No. 0331, VT-5, g-T-7, Aircraft carrier USS Yorktown, NAS Norfolk, Viginia, 1938. Douglas TBD-1 Devastator, Bu.No. 0318, VT-6, 6-T-16, USS Enterprise, December 1940, Hawaii Islands, Pearl Harbor. Douglas TBD1 Devastator, Bu.No. 0300, VT-2, 2-T-1, pilot commander VT-2 Lt.Cdr.W. Stinson, Aircraf...
Aircraft decals (military)
Catalogue
£16.20
£14.58
Out of Stock
0

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
Art Scale 200-D32087
Douglas TBD-1 Devastator - STENCILS The decal sheet contain stencils.
Aircraft decals (military)
Catalogue
£7.80
£7.02
Out of Stock
0

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
Art Scale 200-M32087
North-American B-25H Mitchell canopy frame paint masks (outside only) (designed to be used with Hong Kong Models kits)
Aircraft paint masks (self adhesive)
Catalogue
£11.80
£10.62
Out of Stock
0

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
Art Scale 200-M32088
North-American B-25H Mitchell canopy frame paint mask (inside and outside) (designed to be used with Hong Kong Models kits)
Aircraft paint masks (self adhesive)
Catalogue
£13.99
£12.59
Out of Stock
0

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
Art Scale 200-M32089
Douglas SBD-5/A-24B Dauntless canopy frame paint masks (outside only) (designed to be used with Trumpeter kits)
Aircraft paint masks (self adhesive)
Catalogue
£7.60
£6.84
Out of Stock
0

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
Art Scale 200-M32090
Douglas SBD-5/A-24B Dauntless canopy frame paint mask (inside and outside) (designed to be used with Trumpeter kits)
Aircraft paint masks (self adhesive)
Catalogue
£13.99
£12.59
Out of Stock
0

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
Art Scale 200-M32091
Douglas SBD-3 Dauntless canopy frame paint masks (outside only) (designed to be used with Trumpeter kits)
Aircraft paint masks (self adhesive)
Catalogue
£7.60
£6.84
Out of Stock
0

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
Art Scale 200-M32092
1:32
Douglas Douglas SBD-3 Dauntless canopy frame paint mask (inside and outside) (designed to be used with Trumpeter kits)
Aircraft paint masks (self adhesive)
Catalogue
£13.99
£12.59
Out of Stock
0

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
Art Scale 200-M32044
Dornier Do-335A-10 two seater canopy frame paint masks (outside only) (designed to be used with Hong Kong Models kits)
Aircraft paint masks (self adhesive)
Catalogue
£8.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
Art Scale 200-D32038
Hawker Hurricane Mk.I / Mk.IIc part 12 - Royal Air Force The decal sheet contain 3 markings: - Hawker Hurricane Mk.I, V7101, 69.Sqn, Malta 1941, pilot by Flt Lt. George Burges. - Hawker Hurricane MK IIC, BD939, ZY-S, 247.(F) Squadron. This machine was painted the color of Experimental NIVO. This experimental camouflage used machines FY-S and FY-V. - Hurricane MK I, P2992, P, 527. Calibration Squadron, Hornchurch 1943. This...
Aircraft decals (military)
Catalogue
£11.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

No Image
Art Scale 200-D32061
Aichi D3A1 VAL - STENCILS - Imperial Japanese Army Air Service
Aircraft decals (military)
Catalogue
£11.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

No Image
Art Scale 200-M32079
Heinkel He-111P-1 wheels and canopy frame paint masks (outside only) (designed to be used with Revell kits)
Aircraft paint masks (self adhesive)
Catalogue
£14.00
-
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

No Image
Art Scale 200-M32080
Heinkel He 111P-1 wheels and canopy frame paint mask (inside and outside) (designed to be used with Revell kits)
Aircraft paint masks (self adhesive)
Catalogue
£19.80
-
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
Art Scale 200-M32081
Panavia Tornado GR.4 canopy frame paint masks (outside only) (designed to be used with Italeri kits)
Aircraft paint masks (self adhesive)
Catalogue
£8.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
Art Scale 200-M32082
Panavia Tornado GR.4 canopy frame paint mask (inside and outside) (designed to be used with Italeri kits)
Aircraft paint masks (self adhesive)
Catalogue
£11.99
-
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

No Image
Art Scale 200-M32083
Douglas TBD-1 Devastator canopy frame paint masks (outside only) (designed to be used with Trumpeter kits)
Aircraft paint masks (self adhesive)
Catalogue
£14.00
-
In Stock
2

   1   |   2   |   3   |   4   |   5      »      [6]

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