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.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 14 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
Delivered 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
Print Scale PSL48279
Messerschmitt Bf-109G-6 Part-1 1. Messerschmitt Bf-109G-6 (Red 4) of Oberfeldwebel Rudolf Trenkel 2/JG 52 . Poltava, July 1943. 2. Messerschmitt Bf-109G-6 (White 1) 1.JG 77 aâé-l"Herzasaâé-, Sicily 1943. 3. Messerschmitt Bf-109G-6, "yellow 7", Wilhelm Lemke, 9./JG 3 Bad Worishofen, Germany 1943. 4. Messerschmitt Bf-109G-6 6/JG 52 Bagerovo (Russian Front), December 1943. 5. Messerschmitt Bf-109G-6 of IV./JG 5 aâé-l"Eismee...
Aircraft decals (military)
New Arrivals
19/03/2024
£23.40
£21.06
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
Print Scale PSL48280
1:48
Messerschmitt Bf-109G-6 Part-2 1. Me 109G-6 of 5./JG 53 aâé-l"Pikasaâé- Vienna (Austria), January 1944. Pilot: Oberfeldwebel Herbert Rollwage. 2. Me 109G-6 of Stab I./ JG 27. Fels-am-Wagram (Austria), May 1944. 3. Messerschmitt Bf-109G-6 of 3/JG 300. Yellow 4 Bonn-Hangelar (Germany), spring 1944. 4. Me 109G-6 of 2/JG 300. Red 6 Germany summer 1944. 5. Messerschmitt Bf-109G-6 of 2/JG 301 Germany, winter 1944. 6. Mes...
Aircraft decals (military)
New Arrivals
19/03/2024
£17.40
£15.66
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
Print Scale PSL48281
Focke-Wulf Fw-190 A-3, A-4, A-5, A-6, F & Recon Part-1 1. Fw-190A-3. Unit/Location: 7./JG 51, Vyazma/Russia, December 1942 . Code: White 1 Pilot: CO Gƒ¼nther Schack. Air victories: 174 in WWII. Fate: Survived Merits: Knights Cross & Oak Leave. 2. Fw-190A-4. Unit/Location: 6./JG 54, Petschur/Russia, March 1943. Code: Yellow 2 Pilot: Heinrich Sterr. Air victories: 130 in WWII (127 vs. the VVS). Fate: KIA 26.11.44 in France ...
Aircraft decals (military)
New Arrivals
19/03/2024
£17.40
£15.66
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
Print Scale PSL48285
Lockheed F-80 Shooting Star Part 6 1. Lockheed F-80C-10 Shooting Star 49-665/FT-665, flown by Col Irwin H Dregne, commanding officer of the 51st Fighter Intercept Group, Tsuiki AB, Japan, July 1951.
Aircraft decals (military)
New Arrivals
19/03/2024
£17.40
£15.66
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
Print Scale PSL48286
1:48
Lockheed F-80 Shooting Star Part 7 / 1. Lockheed F-80C-10 Shooting Star 49-423/FT-423, flown by Col. Levi R. Chase, commanding officer of the 8th Fighter Bomber Group, Suwon AB, South Korea, May 1952.
Aircraft decals (military)
New Arrivals
19/03/2024
£17.40
£15.66
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
Print Scale PSL48287
1:48
Lockheed F-80 Shooting Star Part 8 1. Lockheed F-80C-10-LO Shooting Star 49-580/FT-580, flown by Col. Walter G. Benz, Jr.,commanding officer of the 8th Fighter Bomber Wing, Suwon AB, South Korea, October 1952.
Aircraft decals (military)
New Arrivals
19/03/2024
£17.40
£15.66
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
Print Scale PSL48257
1:48
Focke-Wulf Fw-190D-9 Part-1 1. Fw-190D-9 from ll./JG 6. Halle, Germany, May 1945. 2. Fw-190D-9 (W.Nr. 500576) from ll./JG 6. Furth, Germany, May 1945. 3. Fw-190D-9 from the Stab of lV./JG 3. Prenzlau, Germany, March 1945. 4. Fw-190D-9 from the Stab of JG 4. Juterbord-Damm, Germany, February 1945. 5. Fw-190D-9 (W.Nr.211164) from the Stab of JG 300. Prague-Rusin, May 1945.
Aircraft decals (military)
New Arrivals
01/03/2024
£17.40
£15.66
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
Print Scale PSL48258
Focke-Wulf Fw-190D-9 Part-2 1. Fw 109D-9 from the Stab of lV./JG 3. 2. Fw 109D-9 from ll./JG 6. Western Front, February 1945. 3. Fw-190D-9 from 7/JG 2. Rommelhausen-Stockheim, Germany, beginning of 1945. 4. Fw-190D-9 from the Stab of JG 4. Rhein am Main, Germany, beginning of 1945. 5. Fw-190D-9 (W.Nr.211115) from 16./EKG (J), abandoned at Pilsen in Czechoslovakia. 6. Fw-190D-9 from the Stab of lll./JG 2. Consid...
Aircraft decals (military)
New Arrivals
01/03/2024
£17.40
£15.66
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
Print Scale PSL48235
1:48
Lockheed F-80 Shooting Star Part 5 1. Lockheed F-80C-10 Shooting Star 49-653/ FT-653, flown by Col. William T. Samways, commanding officer of the 8th Fighter Bomber Group, Itazuke AB, Japan, June 1950.
Aircraft decals (military)
New Arrivals
29/02/2024
£17.40
£15.66
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
Print Scale PSL48271
Republic P-47 Thunderbolt Part 2 1. P-47D-1-RE "Boise Bee" Unit: 334th FS, 4th FG, 8th AF, USAAF Serial: QP-B (42-7890). 2. P-47C-5-RE Unit: 334th FS, 4th FG, 8th AF, USAAF Serial: QP-J (41-6576).
Aircraft decals (military)
New Arrivals
29/02/2024
£17.40
£15.66
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
Print Scale PSL48273
Mitsubishi Ki-21 Part 1 1. Ki-21-ll Ko(a) Hamamatsu 2 Chutai Based at the Hamamatsu Flying School the tail markings have a white band through them which was a standard practice for this unit. 2. Ki-21-ll Otsu (b) 1Sentai 2 Chutai 1Sentai operated throughout Malaysia and the Philippines during 1942. This aircraft shows yet a different application of camouflage with continual bands sprayed on all upper surfaces. 3. Ki-21-...
Aircraft decals (military)
New Arrivals
29/02/2024
£17.40
£15.66
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
Print Scale PSL48275
Sopwith 1 1/2 Strutter_decals Part 1 1. The French-built Sopwith 1.A2 served in early March 1918 with Escadrille Sop. 24 in Toul. Some Strutters of this unit also carried a third Lewis machine gun above the upper mounting surface. It is possible that this aircraft also carried the armament of three machine guns at some period of its service. The unit was re-equipped with Salmson 2.A2 aircraft on March 14, 1918, and the uni...
Aircraft decals (military)
New Arrivals
29/02/2024
£17.40
£15.66
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
Print Scale PSL48276
Sopwith 1 1/2 Strutter_decals Part 2 1. Sopwith B1, number 185 of Escadrille Sop 107, which operated as part of Groupe dBombardmet 3 (Gb3) from June 1917 until re-equipping with Breguet 14s in late 1917. At a time and in circumstances unknown to the artist, this aircraft was captured dy the Germans. The unit lnsignia was based on the Egyptian goddess Buto. 2. Sopwith A2, 4189 of Escadrille Sop 5, which flew this type f...
Aircraft decals (military)
New Arrivals
29/02/2024
£17.40
£15.66
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
Print Scale PSL48278
1:48
Fokker Dr.I Part 2 1.Fokker Dr.l 195/17 from Lt. Hermann Vallendor, Jasta Boelcke, Halluin-Ost, May 1918. 2. Fokker Dr.l 209/17 of Vzfw. Paul Baumer, Jasta Boelcke, Marcke, March 1918. 3. Fokker Dr.1 403/17 by Lt. John Janzen, Jasta 6, Lien St Amand, February 1918.
Aircraft decals (military)
New Arrivals
29/02/2024
£16.50
£14.85
Out of Stock
0


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