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 = 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 22 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 LL72039
North-American B-25C/D Mitchell Part 1 NEW TOOLING 2018 and Hasegawa B-25J 38BG B-25 *B-25C, 41-12905, 'Tokyo Sleeper' , 405BS/38BG (dragon head emblem) *B-25J, 43-27957, 'Bugs Bunny' , 823BS/38BG (tiger head emblem) *B-25J, 43-28145, 'Estrallita' , 71BS/38BG (wolf head emblem) *B-25J, 44-29284, no name, 822BS/38BG (black panther emblem) *B-25D, 41-30597, 'Hardships 2nd ', hack of 38BG
Aircraft decals (military)
Catalogue
£13.50
-
In Stock
6

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 LL72041
1:72
North-American B-25C/D Mitchell Part 3 NEW TOOLING 2018 and Hasegawa B-25J mainly MTO B-25 *B-25J, 43-3953, #32, 'Problem Child' , 82BS/12BG (nose art on left) *B-25J, 43-27543, 'Form 1A/Dear Arabella' , 381BS/310BG (nose art on both sides) *B-25C,42-32530, 'Rosie' , 487BS/340BG (nose art on right side) *B-25J, 43-27696, 'Baby Blue Eyes' , 446BS/321BG (4 marking states covered, nose art on left side)
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

Lifelike LL72042
1:72
North-American B-25C/D Mitchell Part 4 For Airfix NEW TOOLING 2018 and Hasegawa B-25J Mitchell *B-25D, 41-30669, 'Chow Hound' , hack of 345BG (elaborate nose art, type I emblem) *B-25D, 41-30606, 'Chow Hound Junior' , hack of 345BG (another elaborate nose art, type II emblem) *B-25J, 43-28016, 'Male Call' , 390BS/42BG (nose art, Crusaders emblem) *B-25J, 44-29509, 'My Buck' , 17RS/71RG (nose art and emblem)
Aircraft decals (military)
Catalogue
£13.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 LL72037
1:72
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
£10.50
-
In Stock
10

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 LL72038
1:72
North-American P-51B/P-51D Mustang Pt.4 Jeanne III, Detroit Miss, Contrary Mary, Jackie,
Aircraft decals (military)
Catalogue
£10.50
-
In Stock
12

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 LL72-SP-01
1:72
' Desert Warrior' correction markings for the Airfix North-American B-25C/D Mitchell kit (designed to be used with Airfix kits)
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

Lifelike LL72035
1:72
Nakajima Ki-27 Type 97 'Nate' Part 2 (6) 5th Sentai; 64th Sentai; 10th Independent Chutai; 50th Sentai; 24th Sentai
Aircraft decals (military)
Catalogue
£9.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 LL72036
Nakajima Ki-27 Type 97 'Nate' Part 3 (7) 11th Sentai; 68th Shinbu-tai; 13th Sentai; 47th Sentai; 11th Sentai; 1st Sentai
Aircraft decals (military)
Catalogue
£9.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 LL72028
1:72
Consolidated B-24 Liberator. A/C #1--F-7A, 'American Beauty' of 5AF/5BG/20PMG A/C #2--B-24L-5-CO, 'Air Pocket' of 5AF/90BG/320BS A/C #3--B-24J-190-CO, 'Barbara Jean' of 5AF/43BG/65BS A/C #4--B-24L-10-CO, 'Maiden Montana', #549 of 13AF/5BG/23BS
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 LL72029
1:72
Consolidated B-24 Liberator A/C #1--B-24H-15-CF, 'Double Trouble', '4Z-H+ 'of 8AF/467BG/791BS A/C #2--B-24D-CO, 'Lil' De-Icer', 'I' of 9AF/98BG/415BS A/C #3--F-7A, 'Over Exposed', '2-I' of 311PW/2PMS A/C #4--B-24J-185-CO, 'Shy-Chi Baby' of 5AF/380BG/531BS
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 LL72030
1:72
Consolidated B-24 Liberator A/C #1--B-24D-26-CO, 'Doodlebug', '#432' of 14AF/308BG/373BS A/C #2--B-24J-170-CO, 'Going My Way' of 7AF/11BG/431BS A/C #3--B-24H-15-FO, 'Pistol Packing Mama', 'Y' of 15AF/464BG/776BS A/C #4--B-24M-5-CO, 'Puddle Jumper II' of 7AF/30BG/819BS
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 LL72031
1:72
Consolidated B-24 Liberator A/C #1--B-24D-20-CO, 'The Goon' of 14AF/308BG/374BS A/C #2--B-24J-110-CO, 'Shoo Shoo Baby', '#984' of 5AF/22BG/408BS A/C #3--B-24L-15-FO, 'Mickey Finn' of 15AF/459BG/758BS A/C #4--B-24M-15-FO, 'Sleepy-Time Gal', '#47' of 10AF/7BG/493BS
Aircraft decals (military)
Catalogue
£11.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

Lifelike LL72032
1:72
North-American P-51B/P-51D Mustang A/C #1--P-51B, QP-L, 'Salem Representative' of Lt. Ralph K. Hofer, 4FG/334FS A/C #2--P-51D, B3-Y, 'Skyczar' of Capt. Fred Munder Jr., 363FG/381FS A/C #3--P-51B, C3-D, 'BogMac Junior' of Capt. John Brown, 363FG/382FS A/C #4--P-51D, E6-A, 'Nancy Lee' of Lt. Richard Baggett, 370FG/402FS (
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 LL72033
1:72
Republic P-47D Thunderbolt A/C #1--P-47D, O7-R, 'Bloom's Tomb' of Capt. Jay van Bloom, 406FG/514FS A/C #2--P-47D, UN-S, 'Tinkle' of Lt. Samuel Stamps, 56FG/63FS A/C #3--P-47D, 2Z-V, 'Rain Dear' of Lt. Clarke bresmeth, 405FG/510FS
Aircraft decals (military)
Catalogue
£10.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

Lifelike LL72026
1:72
Kawasaki ki-100-I/Ki-100-II Hien Part 1.
Aircraft decals (military)
Catalogue
£9.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 LL72023
1:72
Supermarine Spitfire Mk.Vb Part 3 (4) AD196 XR-Q 71 Sqn Sgt Jack Eavans; EN824 FU-U 453 Sqn P/O John Yarra. Both Dk Green/Ocean Grey/Mid Sea Grey. Mk.Vb Tropical version WD-A USAAC 4FS 52FG F/O James Montgomery; ER821 QJ-R 92 Sqn F/L Neville Duke Both Dk Earth, Stone/Azure Blue
Aircraft decals (military)
Catalogue
£9.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 LL72024
1:72
Supermarine Spitfire Part 4 (4) Mk.Va W3185 D-B W/C Douglas Bader Tangmeer Wing; Mk.Vb WD-A USAAC 4FS 52FG F/O James Montgomery; Mk.XVI SL721 JMR ACM James Robb Overall blue; TD248 BAC Aviation Natural metal with red fuselage band
Aircraft decals (military)
Catalogue
£9.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 LL72019
1:72
Messerschmitt Bf-109 Part 3 (4) Bf-109E-1 Black 13 2/JG 77 France 1940; Bf-109F-2 White 9 Oberleutnant Hans Phillipp; Bf-109G-6 White 15 1/Jagdgeschwader JG 300 Otto Leisner; Bf-109G-6 White 1+-7/JG 52 Hungary 1945.
Aircraft decals (military)
Catalogue
£9.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 LL72020
1:72
Messerschmitt Bf-109 Py 4 (5) Bf-109E-3 Black A JG 26 Oblt Hassemann; Bf-109G-2 Trop Black << 1/JG 77 Hptm Heinz Bar; Bf-109G-5 Grey << II/JG 11 Maj Gunther Specht; BF-0G-6 White 1 JG 800
Aircraft decals (military)
Catalogue
£9.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 LL72018
Messerschmitt Bf-109F-4 Tropical. H.J.Marseille special. Includes decals for his Kubelwagen 'Otto' and 4 'Yellow 14' aircraft. W Nr 8673 with G Type propeller; W Nr 10137; W Nr 10059; W Nr 8693
Aircraft decals (military)
Catalogue
£9.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 LL72014
1:72
Boeing B-17F/B-17G Flying Fortress Part 1 8th Air Force
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 LL72015
1:72
Boeing B-17G Flying Fortress part 2. 8th Air Force
Aircraft decals (military)
Catalogue
£10.50
-
In Stock
5


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