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=8124 AND ps.id = 956
		 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 20 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

North-American P-51 Mustangs (7) WAS é£10.25. TEMPORARILY SAVE 1/3RD!!! Mk.I AG522 SY-L 613 (City of Manchester) Sqn; AG366 RM-T 26 Sqn Duxford 1942; AG645 RZ-W 241 Sqn; Mk.III KH616 CV-J 3 Sqn RAAF Italy 1944/45; Mk.IV No 9570 AC-S 402 City of Winnipeg) Sqn RC Air Force; NZ3406 Territorial Air Force RNZAF 1950s; KM272 QV-V 19 Sqn 'Dooleybird'
Aircraft decals (military)
Special Offers
£6.83
-
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

1:72
North-American F-86 Sabres Part 2. WAS é£10.25. TEMPORARILY SAVE 1/3RD!!! RAAF (9) A94-974 3 Sqn Butterworth '68; A94-942 76 Sqn Red Diamonds Aerobatic Team 1962; A94-946 ARDU Laverton 1960; A94-949 2(F) OCU Williamstown 1960's; A94-366 5(F) OCU 1960's; A94-974 77 Sqn Butterworth; A94-949 2(F) OCU; A94-954 2(F) OCU both Williamstown 1960's; A94-988 5(F) OCU
Aircraft decals (military)
Special Offers
£6.83
-
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

1:72
Photo Reconnaisance Griffon Engined Supermarine Spitfire (8) WAS é£10.25. TEMPORARILY SAVE 1/3RD!!! RAF FR Mk.18 TZ233/T 208 Sqn Egypt 1949; TP445/H 28 Sqn Hong Kong 1950; PR Mk.19 PS852 81 Sqn Hong Kong 1951; PM574 681 Sqn Burma 1948; PM660 OI-X 2 Sqn Furstenfeldbuck 1948; Swedish Air Force No 31040/60 1Division 1955; Royal Thai Air Force PS888 1855; FR XIVe TP339/96 Royal Indian Air Force 1947
Aircraft decals (military)
Special Offers
£6.83
-
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

1:72
Early BAC/EE Lightnings (6) WAS é£10.25. TEMPORARILY SAVE 1/3RD!!! F Mk.1 XM137/F Air Fighting Development Sqn 1961; XM144 60 Maintenance Unit 1966; XM144 Wattisham Target Facilities Flight 1960; XM190/190 145 Sqn 1965 Red spine-red/white fin; XM144/144 Leuchars Training Facilities Flight 1972; F Mk.3 XP969/969 27 Sqn 228 OCU White fin RAF Coltishall 1970s. All overall natural metal
Aircraft decals (military)
Special Offers
£6.83
-
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

1:72
RAF Germany during 1970's. WAS é£11.25. NOW BEING CLEARED!! SAVE 1/3RD!!! BAC/EE Lightning F.2A XN793 / A of 92 Squadron, based at RAF Guttersloh, West Germany, in 1972. Flown by the unit CO, W/Cdr. J.B. Mitchell. BAe Harrier GR.1 XV781 / C of 4 Squadron, based at RAF Wildenrath, West Germany, in 1971. BAe Harrier T.2 XZ145 / T of 3 Squadron, based at RAF Wildenrath, West Germany, in 1976. Westland Wessex HC.2 XR499 / ...
Aircraft decals (military)
Special Offers
£7.50
-
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

1:72
RAF Germany 1970-75 part 2. WAS é£11.25. NOW BEING CLEARED!!! McDonnell-Phantom FGR.2, VX473 of 14 Squadron, based at RAF Bruggen, West Germany, in 1972. McDonnell-Phantom FGR.2, XV484 of 31 Squadron, based at RAF Bruggen, West Germany, in 1972. Blackburn/Hawker-Siddeley Buccaneer S.2, XW526 of 15 Squadron, based at RAF Laarbruch, West Germany, in 1970. Blackburn/Hawker-Siddeley Buccaneer S.2, XW534 of 15 Squadron, base...
Aircraft decals (military)
Special Offers
£7.50
-
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

RAF Germany, 1970-75 part 3. WAS é£11.25! TEMPORARILY SAVE 1/3RD!!! Hawker Hunter T.7A, WV318 of 15 Squadron, based at RAF Laarbruch, West Germany, in 1975. Blackburn/Hawker-Siddeley Buccaneer S.2, XW527 of 16 Squadron, based RAF Laarbruch, West Germany, in 1974. McDonnell-Douglas FGR.2 Phantom , XV439/439 of 14 Squadron, based at RAF Bruggen, West Germany, in 1970. BAe Harrier GR.1, XV791 of 4 Squadron, based at RAF Wildenrat...
Aircraft decals (military)
Special Offers
£7.50
-
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

1:72
Wings Of Stars Part I. Boeing P-26A/P-26C WAS é£11.25. TEMPORARILY SAVE 1/3RD!!! Peashooter of 18th Pursuit Group, Hawaii 1939; Boeing P-21E of 19th Pursuit Squadron, Hawaii 1933; Spad XIII of 17th Pursuit Squadron, Michigan 1922-23; Curtiss P-36A Hawk of 55th; Pursuit Squadron, Louisiana 1939; Bell P-39D Airacobra HQ Flight, 31st Pursuit Group during the 'Carolina Manoevres' 1941; Curtiss P-40E Kittyhawk/Warhawk o...
Aircraft decals (military)
Special Offers
£7.50
-
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

Wings Of Stars Part 2 (6) WAS é£11.25. TEMPORARILY SAVE 1/3RD!!! Nieuport N.28 6507 213th Aero Sqn Florida 1919; Curtiss P-6E Hawk, 22 33rd PS Virginia Beach 1934; Boeing P-12E 95th PG California 1934; Seversky P-35 of 27th PS Selfridge 1937; Bell P-39D Airacobra 40th PS 'Carolina Manoeuvres' 1941; Curtiss P-40B Tomahawk 55th PS Hamilton Field 1940.
Aircraft decals (military)
Special Offers
£7.50
-
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

McDonnell QF-4S Phantom 3832 VX-30 Bloodhounds The Last of the Legend WAS é£9.20. TEMPORARILY UNDER HALF PRICE!!!
Aircraft decals (military)
Special Offers
£3.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

Bell CH-146 Griffon Canadian Air Force (2) WAS é£7.80. TEMPORARILY SAVE 1/3RD!!! 146493 417 Sqn SAR scheme overall yellow with red trim; 146400 427 Sqn ZL-K 60th Anniversary 2002 overall black [Bell 412 Griffin]
Aircraft decals (military)
Special Offers
£5.20
-
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

BAe Sea Harrier FA.2 (1) 800NAS Decommissioning scheme 2004 with red trim on top and bottom of wings and tail WAS é£7.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£5.20
-
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

BAe Sea Harrier FA.2 (1) ZH809 899 NAS 25th Anniversary special Overall blue with white undersurfaces (BISMay22) WAS é£7.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£5.20
-
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

Dassault-Mirage F.1B D-Day 60th Anniversary Special June 2004. 33-FD with black/white D-Day stripes USUALLY é£11.70. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£7.80
-
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

RAF Display Hawks 2003/2004 (2) XX250 100 Sqn RAF St Athan 100 years of flight Anniversary 2003 Display Season; XX261 BAe Hawk 30th Anniversary 2004 Display Season WAS é£7.80. TEMPORARILY SAVE MORE THAN 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Boeing KC-135E (1) 71431 141 ARS/108 ARW New Jersey ANG 2004 Tiger Meet WAS é£9.20. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£6.13
-
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

Hawker Tempest Mk.II/F.2/F.6 Post War (4) WAS é£7.80. TEMPORARILY SAVE 1/3RD!!! Mk.II PR790 EG-D 16 Sqn RAF Fasburg, Germany 1946; F.2 PR533 5R-V 33 Sqn Overall Aluminium; PR772 5R-D 33 Sqn Camouflage Both RAF Kuala Lumpur 1949; F.6 NX185/O 213 Sqn RAF Deversoir, Egypt 1949
Aircraft decals (military)
Special Offers
£5.20
-
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

Defending the Reich Part 1. (5) Messerschmitt Bf-109G-14 White 1 9/JG 28 France 1944; Black 4 yellow/white/yellow tail bands; Focke-Wulf Fw-190A-9 Red 22 6/JG 301; Focke-Wulf Fw-190D-9 Black
Aircraft decals (military)
Special Offers
£4.47
-
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

Defending the Reich Part 2. (5) Messerschmitt Bf-109G White 4 Unknown unit; Green 1 1/JG 77 White/green tail bands; Focke-Wulf Fw-190A-8 Black > Stab 1/JG 301; Focke-Wulf Fw-190F-8 Yellow 11 11?SG 2; Focke-Wulf Fw-190D-9 White 15 Unknown unit (BISMay22) WAS é£6.70. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.47
-
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

1:72
BAe Nimrod MR.1 Stencil Data (Current Grey Scheme) [BAe Nimrod MR.1 / MR.2 / MR.2P / R.1.] [HC.4 HAR.3] WAS é£7.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£5.20
-
In Stock
5+