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 = 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 60 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
Berna Decals BER32084
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
£13.80
£12.42
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 BER32069
1:32
Re-printed! Dassault-Mirage 2000-5F 'Cigognes' (Storks): 2-AX Né°77 Luxeuil 2019, 2-EC Né°78 Dijon 2004, 116-EC Né°78 Luxeuil 2014 (3 schemes)
Aircraft decals (military)
Catalogue
£15.40
-
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 BER32006N
Reprinted and modified: 1/32 Supermarine Spitfire LF Mk.IX French in Indochina (3 schemes)
Aircraft decals (military)
Catalogue
£16.20
-
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 BER32034N
Reprinted and modified: 1/32 Bell P-39 'Limousin', 'Travail' & 'Auvergne' (3 schemes) & Curtiss P-40 'Lafayette' (2 schemes) in French service
Aircraft decals (military)
Catalogue
£14.60
-
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 BER32072
North-American F-100D French 11-MB (1 scheme), & F-100F 11-RX, 3-IX (2 schemes)
Aircraft decals (military)
Catalogue
£25.99
-
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 BER32083
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
£14.60
-
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 BER32035
Republic P-47D Thunderbolt 'Bubbletop' French 1944-45 : 'Ardennes', 'Lafayette', Dauphine', 'Roussillon' (6+1 schemes)
Aircraft decals (military)
Catalogue
£15.40
-
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
Berna Decals BER32004N
Re-printed! Grumman F6F-5 French Hellcat in Indochina, France & Tunisia (4 schemes)
Aircraft decals (military)
Catalogue
£14.60
-
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 BER32081
de Havilland Vampire Mk.5 Aeronavale (French Fleet Air Arm): 57.S-6 Port Lyautey 1960; 57.S-15, 57.S-7 & 57.S-14 Khourigba 1954-56.
Aircraft decals (military)
Catalogue
£13.80
-
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 BER32082
de Havilland Vampire Mk.5 Armeƒ© de l'air (French Air Army): 5-AG / VZ166 Dijon 1950; TU nƒšé°10094 Meknes 1951; U nƒšé°10080 Sidi-Ahmed 1956.
Aircraft decals (military)
Catalogue
£14.60
-
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 BER32008
1:32
Re-printed! de Havilland Sea Venom FAW.21 (2)/FAW.22 (1) Royal Navy (3 total) WM577/O 891 NAS Ford 1955; WW189 451/J 892 NAS H.M.S. Eagle (Suez) 1956 with yellow/black Suez stripes (not included); XG737 438/B 891 NAS H.M.S. Bulwark 1958 red/white check wingtip tanks
Aircraft decals (military)
Catalogue
£14.60
-
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 BER32029
1:32
Re-printed! Douglas AD-4 Skyraider French : 20-LW Algeria 1961, 21-LW Djibouti 1963 & 20-FX Algeria 1961
Aircraft decals (military)
Catalogue
£14.60
-
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 BER32079
Vought AU-1 & F4U-7 Corsair Aĩronavale : Flottille 12F Aircraft Carrier La Fayette (Indochina - 1955), Flottille 14F (Indochina - 1954), 15F Aircraft Carrier Arromanches (Suez - 1956) (4 schemes)
Aircraft decals (military)
Catalogue
£19.65
-
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 BER32080
1:32
Vought AU-1 & F4U-7 Corsair Aƒ©ronavale : Flottille 12F (Algeria & Cuers-Pierrefeu - 1962-64), Flottille 14F (Algeria - 1963), 17F (Tunisia - 1962), Service Squadrons 10S & 57S (Algeria - 1960 & Frƒ©jus-Saint-Raphaƒ«l - 1957) (5 schemes)
Aircraft decals (military)
Catalogue
£19.65
-
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 BER32078
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
£13.80
-
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 BER32076
French de Havilland D.H.82 Tiger Moth: EC 2/595 Tong (Indochina) 1941, NL867 Meknes (Morocco) 1946 & NL924 Cognac (France) 1946
Aircraft decals (military)
Catalogue
£13.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
Berna Decals BER32077
French Curtiss SB2C-5 Helldiver over Indochina: 3.F-1 & 3.F-16 Bach MaƒÆ'é¯, Indochina april 1954, 9.F-9 & 9.F-7 Aircraft carrier Arromanches, Indochina november 1952
Aircraft decals (military)
Catalogue
£13.80
-
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 BER32075
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
£15.40
-
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 BER32030N
Re-printed French Grumman Bearcat over Indochina : E and T of GC I/9 'Limousin' Cat-Bi 1951-52, F of GC II/8 ' Languedoc' Cat-Bi 1952-53, A of GC I/21 'Artois' Bach-MaƒÆ'é¯ 1953, H of Erom 80 Bach-MaƒÆ'é¯ 1954
Aircraft decals (military)
Catalogue
£15.40
-
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
Berna Decals BER32061N
Re-printed Avro Lancaster Mk.I & Mk.VII Aeronautique Navale : WU-15 (9.S) Tontouta (New Caledonia) 1962, WU-12 (24.F1) Lann-Bihoue 1953, WU-09 (55.S.10) Agadir (Morocco) 1956, WU-03 (2.F.2) Port-Lyautey (Morocco) 1952, WU-14 (4.S.4) Karouba (Tunisia) 1958 & WU-49 (10S ?). [Avro Lancaster B.Mk.I/III]
Aircraft decals (military)
Catalogue
£28.99
-
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 BER32073
1:32
Dassault-Mirage 2000D: EC 1/3 'Navarre' Nancy-Ochey & Kosovo + Stencils (3 schemes)
Aircraft decals (military)
Catalogue
£15.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
Berna Decals BER32074
1:32
Dassault-Mirage 2000D: EC 2/3 'Champagne' & EC 3/3 'Ardennes' Nancy-Ochey & Istrana, EC 4/33 'Vexin' Djibouti (7 schemes).
Aircraft decals (military)
Catalogue
£13.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
Berna Decals BER32071
Douglas A-26B Invader Indochina (2 schemes) + Algeria
Aircraft decals (military)
Catalogue
£15.40
-
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 BER32070
Dassault-Mirage 2000-5F 'Cigognes' (storks) Part 2: 2-EJ Né°42 with the three storks badge, Luxeuil 2020. And 3 additional storks badges so as to make most planes of second wing. 2-ER Né°68 Emirates 2009, 102-ER Né°68 Malta 2011, 116-ER Né°68 Luxeuil 2012 (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 BER32040
1:32
Re-printed! Dassault-Mirage IIIR & IIIRD: 33-CL (IIIR) 'Belfort' Strasbourg-Entzheim 1984, 33-TE (IIIRD) 'Moselle' Strasbourg-Entzheim 1978, 33-TC (IIIRD) 'Moselle' Strasbourg-Entzheim 1980
Aircraft decals (military)
Catalogue
£12.99
-
In Stock
3

   1   |   2   |   3      »      [3]

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