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=357684
		 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 27 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

Colorado CA14402
1:144
Airbus A310 TAP Air Portugal includes all registrations CS-The/I/J/W/X/Z and names WAS é£6.99. NOW BEING CLEARED!! HALF PRICE!!!
Aircraft decals (civil)
Special Offers
£3.49
-
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

Colorado CA32005
1:32
m/s/grey RAF Code Numbers 48 inches (RAF codes/RAF code letters/RAF serial numbers) WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA32010
1:32
Sky RAF Code Numbers 48 inches (RAF codes/RAF code letters/RAF serial numbers) WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48003
1:48
French A.F Codes /Numerals WAS é£6.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48019
1:48
Morane-Saulnier MS.406C1 Part 1. (3) No 231 L593/5 6 Esc Groupe de Chasse III/7; No 113 N432/6 Groupe de Chasse II/3; No 289 N701/2 Groupe de Chasse II/2 WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48023
1:48
North-American P-51 Mustang (3) North-American P-51C 44-10898 ER 2/33 Dijon 1945; Mustang III FB241 GA-Q 112FBS Italy 1944; North-American P-51D Mustang 44-14292 QP-A 334FS Man O'War WAS é£6.99. BEING CLEARED!! SAVE MORE THAN 1/3RD!!!
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

Colorado CA48024
1:48
North-American P-51 Mustang (3) North-American P-51B Mustang 42-6750 VF-5 5FS Italy 44; Mustang III FB353 PK-H 316 Polish Sqn RAF Coltishall; North-American P-51D Mustang 44-4827 E2-V 375FS Croghan Cruiser WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48034
1:48
'Cruz de Cristo' Portuguese National Insignia 1952-80 with Blue outline WAS é£7.99. TEMPORARILY SAVE MORE THAN 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48035
1:48
'Cruz de Cristo' Portuguese National Insignia 1960-80 with Blue outline WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48036
1:48
Northrop F-5B/F-5E/F-5F (4) Northrop F-5B No 4501 Mexico 1984; Northrop F-5E No 808 Chile 1990; No 4820/20 Brazil 1985; Northrop F-5F No201 Sudan Air Force WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48041
Black Code Letters and Numbers 45 degree corners 2 sizes. 1.00m, 1.25m Double sheet NOW BEING CLEARED! WERE é£6.99!!
Aircraft decals (military)
Special Offers
£1.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

Colorado CA48047
1:48
Grey Code Letters and Numbers 45 degree corners 2 sizes. 1.00m, 1.25m Double sheet BEING CLEARED! WAS é£6.99. SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48055
1:48
North-American T-6G Texan France Pt 1 (5) 51-17157/H10 Pilot School Marrakesh 1959; 51-4915/RM EALA 8/72 Algeria 1957; 49-2993 EALA 7/72 Algeria 1961; 51-14440/WI EALA 13/72 Algeria 1961 Shark mouth; 51-4781 EALA 14/72 Algeria 1960 WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48058
1:48
North-American T-6G Texan (2) USAF 93579 LTA-579 'Scream N' Rebel 6147 TCG Korea 1953; 481737 TA-737 36FBG Black/White stripe tail, Germany 1954. Both overall silver WAS é£6.99. BEING CLEARED!! SAVE MORE THAN 1/3RD!!!
Aircraft decals (military)
Special Offers
£2.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

Colorado CA48059
1:48
North-American T-6G Texan (3) USN 90961 USS Wasp 1950; 51683 JOC Korea 1951; USMC 12120 WD/41 VMT-2 MCAS El Toro 1995. All overall silver WAS é£6.99. TEMPORARILY SAVE MORE THAN 1/3RD!!!
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

Colorado CA48067
1:48
RAF Type A1 Roundels/National Insgnia - 45' - 54' WAS é£6.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48069
1:48
RAF Type B Roundels/National Insignia - 56' - 63' WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48071
1:48
RAF Type C Roundels/National Insignia -16' -32' WAS é£6.99. TEMPORARILY SAVE MORE THAN 1/3RD!!!
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

Colorado CA48074
1:48
France, Roundels, Navy. In five sizes and styles from 19.5mm to 26.5mm National Insignia WAS é£6.99. TEMPORARILY SAVE MORE THAN 1/3RD!!!
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

Colorado CA48085
1:48
International Supermarine Spitfire Part 4 (4) Mk.Ia/Mk.1a N3029 DW-K 610 Sqn Biggin Hill 1940; Mk.IIb P8342 UZ-N 306 (Polish) Sqn 1941; Mk.IX MJ642 3W-11 332 Sqn Royal Netherlands Air Force 1951; (MH434) SM 41 Belgian Air Force WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48095
1:48
Morane-Saulnier MS.406C1 (5) No 559 L538 No 6 CFS 1940; No 47/3 N368 GC 1/7 Algeria 1939; No 334 N744 GC11/7 1940; No 242 Luftwaffe markings for delivery flight to Finland; MS.619 Finnish Air Force 1941 WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA48101
1:48
North-American Harvard Mk.IV (1) Portuguese Air Force 50th Anniversary in Service. Overall dark blue with silver trim [T-6G] WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA72032
1:72
Escadrille 'La Fayette' (4) Spad S VII C1 No S1761 Capt W. Lovell 1917; Republic P-47D Thunderbolt 44-33390 9V-J 89 Missions; Dassault MD-450 Ouragan No 111 4-LH 1954; Dassault-Mirage 2000N No 313 1989
Aircraft decals (military)
Special Offers
£4.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

Colorado CA72034
1:72
Morane-Saulnier MS.406C1 #1 (5) No113/N432 GC II/3 1940; No289/N701 GC II/2 1939; No599/L538 CFS 6 1940; No59/N370 GC I/7 Algeria 1939; No242 Luftwaffe; Finland 1942 WAS é£6.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.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

Colorado CA72040
1:72
Vichy Air Force 1940-42 Part 1. Dewoitine D.520; Morane-Saulnier MS.406; Marcel-Bloch MB.152; Marcel-Bloch MB.174A3 WAS é£7.14. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.76
-
In Stock
5+

   1   |   2      »      [2]

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