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=8091 AND ps.id = 955 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 38 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

Lifelike LL48017
Re-printed! Messerschmitt Bf-109E/Bf-109G Part 3. (4) E Black A+- Oblt Hesselmann JG/26 France 1940 RLM 02/71/65; G White 1 10(N) Moskito/Jagdgeschwader JG 300 1944 RLM 74/75/76; Black << Heinz Bar 1/JG 77, yellow under nose, white tail band RLM 74/75/76; Grey << Gunther Specht 1944 yellow tail band RLM 74/75/76
Aircraft decals (military)
Catalogue
£9.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
Lifelike LL48-SP-01
Mitsubishi A6M2/A6M3 Zero Hinomaru. The definitive Hinomaru sheet
Aircraft decals (military)
Catalogue
£8.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
Lifelike LL48061
1:48
Mitsubishi A6M2/A6M3 Zero part 1 15 pages of instruction (printed on both sides of A4 sheet)
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

No Image
Lifelike LL48062
Mitsubishi A6M2/A6M3 Zero part 2 14 pages of instruction (printed on both sides of A4 sheet)
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

No Image
Lifelike LL48059
1:48
North-American P-51D Mustang x 7 options
Aircraft decals (military)
Catalogue
£14.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
Lifelike LL48060
North-American P-51D Mustang x 6 options
Aircraft decals (military)
Catalogue
£14.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
Lifelike LL48057
Nakajima Ki-27 flown by Capt. Inoue of 1st Sentai *Ki-27 flown by M. Sgt. Ito of 5th Sentai *Ki-27 flown by Capt. Shimada of 11th Sentai *Ki-27 flown by 50th Sentai 3rd Chutai *Ki-27 flown by 76th Shinbu-tai *Ki-27 flown by 84th Independent Chutai *Ki-27 flown by Capt. Kuwabara of 77th Sentai *Ki-27 flown by 111th Fighter Training Unit
Aircraft decals (military)
Catalogue
£12.80
-
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
Lifelike LL48058
Nakajima Ki-27 flown by Lt. Col. Hayashi of 4th Sentai *Ki-27 flown by Major Kato of 64th Sentai *Ki-27 flown by 50th Sentai 1st Chutai *Ki-27 flown by 14th Fighter Training Unit *Ki-27 of some unknown Special Attack Unit *Ki-27 flown by 4th Sentai 2nd Chutai *Ki-27 flown by Capt. Yonaga of 24th Sentai *Ki-27 flown by 1st Lt. Sakai of 64th Sentai
Aircraft decals (military)
Catalogue
£12.80
-
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

Lifelike LL48053
1:48
Mitsubishi A5M4 "Claude"
Aircraft decals (military)
Catalogue
£12.80
-
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

Lifelike LL48054
1:48
Mitsubishi A5M2b/A5M4 "Claude"
Aircraft decals (military)
Catalogue
£12.80
-
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

Lifelike LL48055
Mitsubishi A5M2b/A5M4 "Claude"
Aircraft decals (military)
Catalogue
£12.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

Lifelike LL48051
North-American P-51D Mustang Pt.5 'Big Beautiful Doll' 6 scheme special all piloted by John D. Landers either when he was a Major, Lt. Colonel or a Colonel.
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

Lifelike LL48052
1:48
North-American P-51B/P-51D Mustang Pt.6 Jeanne III, Detroit Miss, Contrary Mary, Jackie,
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

Lifelike LL48034
1:48
Messerschmitt Bf-109E-3 Part 6 (4) Black chevron Stab I/JG 51 Lt E.Terry; Black chevron II/JG 5 Hptm H. Carganico; Bf-109E-2 Black chevron Stab IV/JG 51 Ofw F.Beerenbrock; Black chevron I/JG 53 Hptm E. Hartmann
Aircraft decals (military)
Catalogue
£10.50
-
In Stock
8

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

Lifelike LL48032
1:48
Focke-Wulf Fw-190A Part 3 (4) Three aircraft flown by Major Hermann Graf all with slight variations. Red tulip petals on yellow nose and rudder with red lightning bolt under cockpit. Black 6 3/JG 10 Uffz Gunther Pope with yellow.red snake along fuselage
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

Lifelike LL48005R
1:48
Re-printed and updated! 244th Sentai Part 3.
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

Lifelike LL48050
1:48
Kawasaki Ki-61 part 1 (Hiens kill marks)
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

Lifelike LL48048
North-American P-51D Mustang part 4 (Don Gentile "Shangri-La" (4FG and war bond tour in USA), C. Kinnard "Man O' War", R. Rowland "Dirty Dick")
Aircraft decals (military)
Catalogue
£11.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

Lifelike LL48049
Republic P-47D Thunderbolt part 8 (F. Klibbe "Little Chief", F. Perego "Slick Chick", J. Reams "The Reamer")
Aircraft decals (military)
Catalogue
£11.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

Lifelike LL48015
1:48
North-American P-51D Mustangs Part 1. (4) 415459 HL-B 308 FS/31 FG Capt John Voll `American Beauty/Luvely Lila' Red bands on tail Italy 1944; 414733 CS-L 370 FS/259 FG Capt Raymond Wetmore `Daddy's Girl' Green nose/blue rudder East Wretham 1945; 414570 WD-D 335 FS/4 FG Capt Ted Lines `Thunderbird' 1944 Red nose, D-Day stripes; 44-12017 348 FG Lt Col Bill Dunham `Mrs Bonnie' le Shima/Okinawa 1945
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

Lifelike LL48045
1:48
Republic P-47D Thunderbolt part 7 includes Eileen, Always Marge and one other
Aircraft decals (military)
Catalogue
£11.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

Lifelike LL48046
1:48
Republic P-47D Thunderbolt part 8 Includes Sylvia and 2 others
Aircraft decals (military)
Catalogue
£11.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

Lifelike LL48047
North-American P-51B/P-51D Mustang part 3 Inc Flagship Nancy Ann, Oklahoma Kid plus 2 more
Aircraft decals (military)
Catalogue
£11.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

Lifelike LL48033
Messerschmitt Bf-109 Part 5 (4) Bf-109E-3 Yellow 3 6/JG 51 Fw A.Hasse yellow nose and rudder; Bf-109F-4 Black 1 8./JG 54 Oblt M. Ostermann; Bf-109G-4 White chevron II/JG 11 Major A.Dickfeld; Green chevron NAG-4 Hptm F. Kahler
Aircraft decals (military)
Catalogue
£10.50
-
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

Lifelike LL48043
1:48
Fokker D.VII's. Fokker D. VIIF, 4348/18 of Jasta Boelcke, November 1918. Fokker D.VII (OAW), 4499/18, flown by Vizeflugmeister Franz Mayer, MFJ III, August/September 1918. Fokker D. VIIF, 323/18F, flown by Vizefeldwebel Willy Gabriel, Jasta 11, late June 1918. Fokker D. VII (OAW), 2188/18 (?), flown by Ltn. Hans Koerner, Jasta 19, early 1918 to September 1918.
Aircraft decals (military)
Catalogue
£10.50
-
In Stock
5

   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