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=362108 AND ps.id = 955
		 AND P.product_category_id = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



Found 17 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

Print Scale PSL013
1:48
Austro-Hungarian Lozenge Part 1 Wet decal In the complete set 2 sheets
Aircraft decals (military)
Limited Availability
£17.30
-
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

Print Scale PSL015
1:48
Austro-Hungarian Lozenge Part 2 In the complete set 3 sheets
Aircraft decals (military)
Limited Availability
£21.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

Print Scale PSL48026
1:48
Fokker D.VII Part 2 (6) Jasta 19 Yellow nose; Jasta 18 blue fuselage with blue/white check band; Jasta 13 Lozenge fuselage with black dog on white oval; Lt Heintz von Beaulieu-Marconnay U.10 on wing top and fuselage; Jasta 73 Black/white check fuselage and tail with blue nose and Mimmi on top of wing; Jasta 50 lozenge fuselage with blak/white band. 4 decal sheets including lozenge
Aircraft decals (military)
Limited Availability
£32.20
-
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

Print Scale PSL48028
1:48
Messerschmitt Bf-110 Part.2 (5) Bf-110D Red 65 ZG Schule; Bf-110E 3U+MR ZG 26 1941; Bf-110F-1 LN+FR 10(Z) Staffel ZG 5 1942; Bf-110G-4WD NZG 1 1944; Bf-110C-1
Aircraft decals (military)
Limited Availability
£15.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

Print Scale PSL48057
1:48
Yakovlev Yak-9 1. Yak-9U - flown by Georgiy Baevski, Hero of the Soviet Union (19 personal victories). The 5th Guards Fighter Regiment (GIAP), Germany, March 1945. 2. Yak-9P (Yak-9B) 'Moscow' - tactical number '2' - flown by Captain I.I. Petrov, the commanding officer of the 1st Sq. 168th IAP. Khimki, Moscow, June 13, 1944. 3. Yak-9M - flown by soviet ace pilot G.L. Nakaznyuk. (5 personal and 5 group victories. Partici...
Aircraft decals (military)
Limited Availability
£17.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

Print Scale PSL48092
1:48
Heinkel He-51 Part 1 1. He 51A-1 3/JG 233, Wien-Aspern. 2. He 51B-1 Flown by Stabsartz. Dr. Heinrich Neumann, 5 December 1937. Neumann, a doctor of medicine serving with San/ 88, Flew this aircraft unofficially, but on 5 December 1937 crashed it on landing at El Burgo and badly damaged. 3. He from the Legion Condor to the Spanish crews of Gruppo de Gardena I-G-2 around November 1937.
Aircraft decals (military)
Limited Availability
£15.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

Print Scale PSL48101
1:48
Fieseler Fi-156C Storch
Aircraft decals (military)
Limited Availability
£15.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

Print Scale PSL48107
1:48
Sikorsky UH-60A Black Hawk/MH-60G Pave Hawk
Aircraft decals (military)
Limited Availability
£17.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

Print Scale PSL48113
1:48
Northrop F5E "Tiger II" Iranian Tigers Part 1
Aircraft decals (military)
Limited Availability
£17.70
-
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

Print Scale PSL48114
1:48
Northrop F-5A "Tiger II" Iranian Tigers Part 2
Aircraft decals (military)
Limited Availability
£17.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

Print Scale PSL48133
1:48
Iranian Republic P-47D Thunderbolt
Aircraft decals (military)
Limited Availability
£17.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

Print Scale PSL48169
1:48
Curtiss P-40C CU. Part 2 P-40-CU Unit: 55th PS, 20th PG, USAAC. Serial: 100/20P End of 1940 - beginning of 1941. The aircraft bearing camouflage which was typical of the period, dark olive drab above and neutral grey below, with 'US ARMY' in large letters under the wings. On the top surface of left wing, tactical marking is repeated. This unit was previously equipped with P-36s. P-40-CU Unit: 35th PS, 8th PG, USAAC Serial:...
Aircraft decals (military)
Limited Availability
£17.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
Print Scale PSL48180
de Havilland Tiger Moth Part 2 1. de Havilland D.H.82 Tiger Moth, MO-159, LeLv26, Finnish Air Force, 1943. (Olive Green) upper surfaces and (Very Light Grey) undersides. Undersides of wingtips and fuselage band in Yellow. Codes in black. National markings in six positions. 2. de Havilland D.H.82A Tiger Moth, 12-HI-105/2-I-2, Brazilian Navy. Silver overall with red bands around engine cowling and rear fuselage. Red wheel hu...
Aircraft decals (military)
Limited Availability
£17.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
Print Scale PSL48181
1:48
McDonnell F3H Demon Part-1 1. McDonnell F3H-2 Demon (F-3B), VF-41 "Black Aces", US Navy, 100/AG (BuNo.143479) CVA-62 USS Independence, 1959. 2. F3H Demon, VF-131, US Navy, 104/AE (BuNo.143599) CVA-64 USS Constellation, 1962.
Aircraft decals (military)
Limited Availability
£19.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
Print Scale PSL48182
1:48
McDonnell F3H Demon Part-2 1. McDonnell F3H Demon Cé"é VF-122 "Black Angels", US Navy, F3H-2N (F-3C) , 405/NG (BuNo.137001). 2. F3H-2 Demon, VF-21, US Navy ,203/NE (BuNo.143435), CVA-41 USS Midway, 1959.
Aircraft decals (military)
Limited Availability
£19.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
Print Scale PSL48199
Republic P-47D Thunderbolt Part 3 1. P-47D-1-RE "Miss Plainfield" Unit: 334th FS, 4th FG, 8th AF, USAAF. Serial: QP-D (42-7945). Pilot - Capt.Steve N.Pisanos ('The Greek'). Plainfield, New Jersey. Joined 334th FS 24/10/42 from 71st (Eagle) Squadron RAF. Shot down in a P-51? on 5/3/44, evaded and returned 2/9/44. To 67th FW on 9/9/44. Completed 106 WW2 sorties. To eP-47D-1-RE 42-7945 flown by Steve Pisanos during August 194...
Aircraft decals (military)
Limited Availability
£17.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
Print Scale PSR48025
Packard V-1650 Merlin
Aircraft detailing sets (resin)
Limited Availability
£35.99
-
In Stock
1


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