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=8009 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 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 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
FCM RIO72007
Civil Douglas C-47 VARIG & REAL Aerovias Brasilia
Aircraft decals (military)
Catalogue
£15.75
-
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

FCM FCM72028
1:72
Re-printed! Douglas C-47A Dakota Skytrain (3) USAAC 2100521 J8/I 92 TCS/439 TCG D-Day stripes Upottery, Devon 1944; Brazilian Air Force 2091 4 ETA Caraja 1972; 2041 5 ETA Pegaso 1972;
Aircraft decals (military)
Catalogue
£15.75
-
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

FCM FCM72029
Re-printed! Douglas C-47/Douglas AC-47D (3) Brazil Air Force 2034 1GT `Gordo' 1972; Chile Air Force 963 Gp de Aviacion No 10 1946; 43-010/OS 432 TFW Udorn 1970
Aircraft decals (military)
Catalogue
£15.75
-
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
FCM RIO72002
Treinadores da Aviacao Militar Brasileira Stearman A75L3 1939 Fairchild PT-19 1947 Cessna T-37C Dragonfly x 2 1970 and 1981
Aircraft decals (military)
Catalogue
£10.35
-
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
FCM RIO72004
Luftwaffe insignias
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

No Image
FCM RIO72006
German Lozenges 5 colors upper/lower surfaces
Aircraft decals (military)
Catalogue
£15.75
-
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
FCM FCM72050
Northrop F-5EM / F-5FM Brazilian Air Force
Aircraft decals (military)
Catalogue
£11.25
-
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

FCM FCM72033
1:72
Dassault-Mirage III Pt 1 (3) Dassault-Mirage IIIE BR 4914 1st Ala de Defensa Andrea Anapolis, Brasil 1972 Natural Metal; Dassault-Mirage M5M No 8 Grupo de Aviacion, Antofagasta, Chile 2000 Lt Green/Dk Green/Tan; Dassault-Mirage III L 4th FS, Kleiat, Lebanon 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

FCM FCM72034
1:72
Dassault-Mirage III Part 2 (4) Dassault-Mirage IIIE BR 29/AN 1st G.D.A Anapolis Brasil 1980 or 1985 Blue/Grey; Dassault-Mirage 50EV Grupo Aereo de Caza 11, Venzuela 2992 FS34079/34102/30257; Dassault-Mirage M5DE 69 Sqn 236 FGA Tanta, Egypt 1976 FS30400/34102/35622.
Aircraft decals (military)
Catalogue
£10.60
-
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

FCM FCM72039
1:72
Re-printed! Latin American S-2A/F Trackers (4) Brasil P-16 7028 1/1 Gp de Av Embarcada 1976; P-16 7018 2/1 Gp de Av Embarcada 1976; Argentina 2-AS-4 Esc.Aer Antisubmarino 1965; Uruguay A-851 Escuadron Antisubmarino 1965;
Aircraft decals (military)
Catalogue
£13.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

FCM FCM72047
1:72
Gloster Meteor F.8 and TF-7 (FAB - 10 versions) Version 1 Gloster Meteor F.8 1ƒšéº/14ƒšéº G.Av. - Pampa 4448 Canoas Air base - 1966 - Brasil version 2 Gloster Meteor F.8 1ƒšéº/1ƒšéº G.AvC. - Jambock 4460 Santa Cruz Air base - 1973 - Brasil version 3 Gloster Meteor F.8 1ƒšéº/1ƒšéº G.AvC. - Jambock 4452 Santa Cruz Air base - 1965 - Brasil version 4 Gloster Meteor F.8 2ƒšéº/1ƒšéº G.AvC. - Pif-Paf 4422 Santa...
Aircraft decals (military)
Catalogue
£12.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

FCM FCM72048
Super Lynx (Brazil / Portugal / England) Westland Super Lynx HAS.2 Version 1 Westland SAH-11 Super Lynx - HA-1 - Esq. Lince BAeNSPA - S. Pedro D'Aldeia Rio de Janeiro - Brasil - 1979 version 2 Westland Mk.21A Super Lynx HA-1 - Esq. Lince BAeNSPA - S. Pedro D'Aldeia Rio de Janeiro - Brasil - 2010 version 3 Westland Mk 95 Super Lynx N.R.P. ƒÆ'élvares Cabral Ocean Shield Operation Montijo - Portugal - 2009 ...
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

FCM FCM72020
1:72
Re-printed! Jambock Squadron - Tarquinia 1944. Brazilian Republic P-47D Thunderbolt. Markings for any aircraft flown by Brazil in Italy 1944 as part of the 350th FG. OD/grey camouflage schemes and natural metal finishes.
Aircraft decals (military)
Catalogue
£15.75
-
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

FCM FCMS
1:32
WWII swastikas
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

FCM FCM72032
1:72
South American Lockheed F-80/Lockheed T-33 (8) Lockheed F-80 Brasil Air Force 4200 Esq.Pacau Commander white with red trim; 42-02 4 of Hearts 1959; 4227; all 1 /4 G.Av Fortaleza AB; Lockheed T-33 4351 Esq Pampa Canoas AB 1974; 4347 Pacau 1 /4 G.AV 1967; Mexico Je-052 Esq Aero 202 shark mouth 1967; Portuguese Air Force 1919 Esq 103 1968; Chile J-332 Grupo 12 1971
Aircraft decals (military)
Catalogue
£13.50
-
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

FCM FCM72045
1:72
Douglas A-20B/A-20C/A-20H (FAB, USAAF, SAAF)
Aircraft decals (military)
Catalogue
£11.25
-
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

FCM FCM72046
1:72
Northrop F-5B/F-5E/F-5F (FAB) Tiger II Brazil Air Force
Aircraft decals (military)
Catalogue
£12.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

FCM FCM72023
1:72
Curtiss P-40K/P-40N (10) All 1/14 G.Av Brazilian Air Force 1947 - 1954
Aircraft decals (military)
Catalogue
£11.25
-
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

FCM FCM72038
Dassault-Mirage 2000 (2) 4947 1st GDA Anapolis Brasil 2006 Lt/Dk Blue Grey; 061 Esq 412 GAC 4 La Joya, Peru 2002 FS30118/30277/36375.
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

FCM FCM72031
South American Douglas A-26 Invaders (7) Brasil 5174/10 1/10 G.Av 1972; 5160 Natal Air Base 1969; 5158/58 1/5 G.Av Recife 1970; 5162/52/5 G.Av Natal AB 1962; 5156/10 1/10 G.Av S.Paulo 1972; Chile Red 846 Gr 8 1965; Columbia 2510 1 Esc de Contra Submarino 1951
Aircraft decals (military)
Catalogue
£15.75
-
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

FCM FCM72024
1:72
Re-printed! Curtiss P-40E/P-40L/P-40M/P-40N (6) GA-C 112 Sqn Shark Mouth RAF Egypt 1942; 2105288L 7 FS New Britain; No 24 7 FS New Guinea; No 16 7 FS New Guinea 1943; KH-51 HLeLv 32 Finland 1944; No 44 317 FS 'Lighthouse Louie' Tunisia
Aircraft decals (military)
Catalogue
£13.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

FCM FCM72040
1:72
Grumman F-14A Tomcat 'Sundowners'
Aircraft decals (military)
Catalogue
£13.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

FCM FCM72018
1:72
Fokker Fighters (7) Fokker Dr.I Triplane (1) Jasta 6 Lt Hans Kirchstein, black/white bands on wings and fuselage; Fokker D.II (1) Kest.4b 1916 black/white rear fuselage; Fokker D.VII (5) Jasta 40 Carl Degelow Stag on fuslage; Jasta 18 Vzfw.Glatz, Lt Raben, Lt Kustner all red/white fuselage with bird design. Colourful collection
Aircraft decals (military)
Catalogue
£11.25
-
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

FCM FCM72042
1:72
Grumman F-14A Tomcat Part 2 VF-111 Sundowners (3) 162594 NL/200 CAG USS Kitty Hawk overall Lt Gull Grey with coloured markings; 160668 NL/213; 160690 NL/211 both USS Carl Vinson overall Lt Gull Grey with lo vis markings.
Aircraft decals (military)
Catalogue
£13.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

FCM FCM72017
1:72
Albatros D.III/D.V (6) D.III. Yellow 2 153.255; Jasta 50 Fritz Liese; D.V Jasta 12 Lt Neckel; Jasta 32 Manfred Stimmel; Jasta 18 Paul Strahle; Jasta 5 Fritz Rumey.
Aircraft decals (military)
Catalogue
£11.25
-
In Stock
4

   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