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=304369 AND ps.id = 956 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 84 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
Berna Decals BER72015N
1:72
Re-released!! Dassault Mystere IVA (4) 12-YE EC1/12 Cambrai 1955; 2-FZ EC2/2 Dijon 1956; 2-SF EC3/2 Suez and Dijon 1956; OR Centre de Tir et de Bombardement Cazaux 1959
Aircraft decals (military)
Catalogue
£11.30
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
Berna Decals BER72135
Republic P-47D Colourful Thunderbolts Part 2: P-47D-20-RE 'Raid hot Mama' s/n 42-27181 or 42-76569 - 510th FS / 405th FG - England 05/1944, P-47D-22-RE 'Joan' s/n 42-25893 - 507th FS / 404th FG - Winkton 06/1944.
Aircraft decals (military)
Catalogue
£8.10
£7.29
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

Berna Decals BER72074
Re-printed! Sepecat Jaguar A: 11-EK & 11-MT with 14 bombing missions markings, 11-MV with 4 bombing missions markings and one with a Matra Magic missile, 11-YG aircraft seriously damaged by a SA-7 (right engine destroyed) during the first mission of the Gulf War by French warplanes on 17/01/1991; all aircraft stationed in Saudi Arabia at Al Ahsa early 1991. & 11-YI Bordeaux-Mƒ¯é¿é½rignac 07/1992 (special scheme for the disband...
Aircraft decals (military)
Catalogue
£10.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

Berna Decals BER72075
1:72
Re-printed! Sepecat Jaguar A Part 2 : 11-ES n'A157, Toul-Rosiƒ"res June 1991,18 bombing missons and 3 missiles AS 30L fired during the Gulf War; 11-RJ n'A135, Toul-Rosiƒ"res seen with an AS 37 missile during a detachment in Bangui (Central African Republic) in august 1986; 3-XJ n'A16, Nancy-Ochey seen in flight over Chad, early 1987; 11-RK n'A149, Toul-Rosiƒ"res seen at Nellis AFB (USA) during a Red Flag exercise in March 1990...
Aircraft decals (military)
Catalogue
£11.30
-
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
Berna Decals BER72070R
Re-printed! French Curtiss SB2C-5 Helldiver over Indochina (revised): 3.F-1 & 3.F-16 Bach Maƒ¯é¿é½", Indochina april 1954, 9.F-9 & 9.F-7 Aircraft carrier Arromanches, Indochina november 1952 & 10.S.10 1953.
Aircraft decals (military)
Catalogue
£10.50
-
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

Berna Decals BER72122
Dassault-Mirage 2000C RDM : 330-AS, 2-EF, 2-LH; 2000C RDI : 12-YG, 12-YN, 12-KP; 2000-5F : 2-FK (7 schemes)
Aircraft decals (military)
Catalogue
£11.30
-
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

Berna Decals BER72091
1:72
Re-printed! African Air Forces Dassault-Dornier Dornier Alpha Jets : NAF 473 Nigeria 1990, 5V-MBB & 5V-MBD Togo 1981, QA 53 Qatar 1994 & 2010, 234 & 246 Morocco 1992 & 2000, 3502 Egypt 1982, TJ-XBX & TJ-XCI Cameroon 2005, TU-VCA & TU-VCG Ivory Coast 1995 rƒ¯é¿é½f.
Aircraft decals (military)
Catalogue
£11.30
-
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
Berna Decals BER72133
Republic P-47D Razorback French 1944-45 (4 schemes)
Aircraft decals (military)
Catalogue
£10.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

No Image
Berna Decals BER72134
Republic F-84F Thunderstreak French (4 schemes).
Aircraft decals (military)
Catalogue
£12.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

Berna Decals BER72001
Dewoitine D.520 (2) Alternative versions from Groupe Doret 1944
Aircraft decals (military)
Catalogue
£6.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

No Image
Berna Decals BER72132
Dassault-Mirage IIIB EC 2/2 'CƒÆ'é'te d'Or' : 2-FS Né°208, 2-FI Né°209 & two schemes of 2-FP Né°227 - Dijon 1966-67. The aircraft are appearing in three different episodes of the French TV series 'The Knights of the Sky'.
Aircraft decals (military)
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
Berna Decals BER72131
Dassault-Mirage IIIE Armeƒ© de l'Air (Air Army) Part 3: 13-QA Né°482 - EC 1/13 'Artois' - BA 132 Colmar 1977, 3-IJ Né°505 - EC 1/3 'Navarre' - BA 133 Nancy-Ochey 1979-81
Aircraft decals (military)
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

Berna Decals BER72033
Republic P-47D Thunderbolt bubble top (7) 433663 AF-C GC 3/6 Roussilion 1946; /H GC II/6 Normandie-Nieman 1951; 433405 Eta-Major de le 3eme Esc de Chasse 1946; 433404 GM 1/21 Artois; 420939 10-SC EEC 3/10 1953; 419704 GC 1/10 Cercle de Chasse de Paris 1950; 490024 EC 1/20 Aures-Nementcha 1956
Aircraft decals (military)
Catalogue
£9.70
-
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
Berna Decals BER72054R
Reprinted: 1/72 Dassault Super Mystere B2 Part 3 : Né°154 12-ZJ & Né°132 12-ZQ "Cornouailles", Né°99 12-YJ "Cambresis" & Né°59 10-SD "Valois".
Aircraft decals (military)
Catalogue
£10.50
-
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
Berna Decals BER72078R
Reprinted: 1/72 Dassault Super Mystere B2 Part 4 : Né°151 12-YN 'Cambresis' 1967, Né°173 12-ZO 'Cornouailles' 1969 & Né°02 coded CV from EPNER - Istres 1970.
Aircraft decals (military)
Catalogue
£12.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

Berna Decals BER72081R
Re-printed! Hawker Hurricane Mk.I GC1 'Alsace' Z4757 Capitaine Tulasne 05/1942, Z4434 Fuka 07/1942, Z4230 Aspirant Mailfert Maaten-Baguish 06/1942, BG707 Commandant Pouliquen Suez Canal 09/1942
Aircraft decals (military)
Catalogue
£6.99
-
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

Berna Decals BER72052
Re-printed! Dassault Super Mystere B.2 Part 1 : Né°49 5-NB 'Vendee', Né°88 10-RB 'Seine', & Né°15 H CEAM ref
Aircraft decals (military)
Catalogue
£11.30
-
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

Berna Decals BER72085
Re-printed! North-American T-6G French in Algeria : 51-14933 of EALA 3/1 'Air Pluto & Wasp' in 1961, 51-14718 of EALA 19/72 'Black Scorpion' in 1960, 49-2982 & 51-14839 of EALA 15/72 'Osprey' in 1957 & 1959, 51-15141 of EALA 16/72 'Viper' in 1959
Aircraft decals (military)
Catalogue
£9.70
-
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

Berna Decals BER72025
Re-printed! Dassault-Mirage F.1M Ala de Caza 14, 14-09 Ocean Tiger Meet 08 Landivisiau (1 decoration)
Aircraft decals (military)
Catalogue
£9.70
-
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

Berna Decals BER72088
1:72
Re-printed! North-American T-28S French in Algeria : 51-7812 n'19 & 52-1199 n'13 of EALA 03/009 (12/72) Bone 1961, 51-3569 n'69 of EALA 03/005 Mecheria 1961, 51-3584 n'95 & 52-1231 n'20 of EALA 03/009 (7/72) Bone 1961, 51-3513 n'51 of EALA 03/004 Telergma 1961, & 51-3488 n'40 of EALA 03/010 Batna 1961
Aircraft decals (military)
Catalogue
£9.70
-
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
Berna Decals BER72130
1:72
Republic P-47D Colorful Thunderbolts: P-47D-16-RE 'Touch of Texas' s/n 42-76076 - 510th FS / 405th FG - France 10/1944, P-47D-28-RA 'Dottie Mae' s/n 42-29150 - 511th FS / 405th FG - Belgium 03/1945
Aircraft decals (military)
Catalogue
£8.10
-
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
Berna Decals BER72089M
Re-printed! 1/72 Westland WG-13 Lynx Mk 4 (French Navy) : Né°804 - Flotille 34F - Frƒ©gate Dupleix - Mission ZMOI (Indian Ocean) 2012, Né°808 - CEPA - Hyeres 2011, Né°811 - Flotille 34F - Lanvƒ©oc, Né°276 - Flotille 31F - Frƒ©gate Montcalm - Mission GAN 2006, Né°274 - Flotille 31F - Frƒ©gate Jean de Vienne - Mission ZMOI 2008-2009, Ref. BD 72-89 M
Aircraft decals (military)
Catalogue
£9.70
-
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
Berna Decals BER72128
Sud-Aviation HSS-1 (Sikorsky H-34) : 33F-11 (flotille 33F) Algeria - 1959, 640 (escadrille 20S) Saint-Raphaƒ«l - 1970, 128 (flotille 31F) Saint-Mandrier - 1972, 148 (flotille 33F) Saint-Mandrier - 1976.
Aircraft decals (military)
Catalogue
£10.50
-
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
Berna Decals BER72129
Dassault Super Etendard: 14F Landivisiau 1990 & 2000, 11F & 17F Carrier Foch Kosovo 1999 (7 schemes)
Aircraft decals (military)
Catalogue
£11.30
£10.17
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
Berna Decals BER72127
Dassault-Mirage 2000-5F 'Corse' : 188-EF Né°45 Djibouti 2013, 104-MH Né°67 ('Lorraine') Al-Dhafra (Dubai) 2011, 188-MH Né°67 Djibouti 2013
Aircraft decals (military)
Catalogue
£11.30
-
In Stock
5+

   1   |   2   |   3   |   4      »      [4]

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