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=7880
		 AND P.product_category_id = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



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

ADH Publishing MIM026
No Scale
Military Illustrated Modeller. June 2013 Issue 26 (AFV Edition) The Eight Wheeled Brick - Building Leadwarrior's 1:35 Magirus 'Achtradwagen-Mannschaftstransportwagen' WAS é£6.50. BEING CLEARED!! SAVE 1/3RD!!!
Magazines
Special Offers
£4.33
-
In Stock
1

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

ADH Publishing MIM074
No Scale
Military Illustrated Modeller (issue 74) June '17 (AFV Edition) WAS é£6.50. TEMPORARILY SAVE 1/3RD!!! 4 NEWS Military model product news 6 SUBSCRIBE AND RECEIVE A FREE TOOL SET! A very special MiM subscription offer! 8 'TEN HUT! Eight new military kits on parade 10 ALONG CAME A SPIDER Adding extra details to Hobbyboss's SS-23 Spider Tactical Ballistic Missile 16 ONE CAREFUL OWNER Using a DEF Model conversion to cr...
Magazines
Special Offers
£4.33
-
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

ADH Publishing MIM099
No Scale
Military Illustrated Modeller (issue 99) July '19 (Aircraft Edition) WAS é£6.75. NOW BEING CLEARED!! HUGE SAVING!!! mim099 4 NEWS What's happening in modelling and aviation 6 REFERENCE Buchon HA-1112 Close-Up by Brett Green 10 PREVIEW Classic Airframes 1:48 Focke-Wulf Fw-190 V1 12 LUFT '69 James Hatch converts Hasegawa's 1:32 Bf-109G kit to a Spanish Buchon in BoB markings! 26 PREVIEW Modelsvit 1:48 XF-55 Ascender...
Magazines
Special Offers
£1.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
ADH Publishing MIM120
Military Illustrated Modeller (issue 120) September 2021 (AFV Edition) WAS é£6.75. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS Military modelling news 8 DESERT CRUISER Building, painting and weathering Border Models' 1:35 Crusader Mk.III 18 MSTA OF WAR Zvezda's 2S19M1 'Msta-S' in 1:72 26 PANZERS: WRECKED References images from the WW2 archives COVER STORY 30 THE BEAST FROM THE EAST The 1:35 M1989 Koksan concludes 38 WE...
Magazines
Special Offers
£4.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
ADH Publishing MIM122
Military Illustrated Modeller (issue 122) November 2021 (AFV Edition) WAS é£6.75. BEING CLEARED!! SAVE 1/3RD!!! 'All Roads Lead to Rome - A rebellious Stuart crew might have met their match...' 4 NEWS Military modelling news 8 DAREDEVIL IN WARSAW A scene from the 1944 Warsaw Uprising featuring captured Hetzer "Chwat" 16 APPROACH WITH CAUTION A wrecked Sd.Kfz.251 Ausf.D is inspected by a US GI 18 THE POINT OF NO RE...
Magazines
Special Offers
£4.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
ADH Publishing MIM124
Military Illustrated Modeller (issue 124) January 2022 (AFV Edition) USUALLY é£6.75. NOW ON CLEARANCE!! HUGE SAVING!!! *Moment of respite- a poignant pause on the western front, France 1918* Military modelling news 6 M18 HELLCAT GMC Tamiya's all-new 1:35 kit of the WW2 US M18 Hellcat tank destroyer, previewed COVER STORY 10 A MOMENT OF RESPITE A poignant vignette from 'The War To End All Wars' 18 MINE, ALL MINE ...
Magazines
Special Offers
£1.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
ADH Publishing MIM126
No Scale
Military illustrated Modeller March 2022 (issue 126) WAS é£6.75. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS Military modelling news 8 LITTLE STUART! Building, painting and weathering AFV Club's 1:35 M5A1 Stuart Light Tank 20 NEW BUILD VEHICLE! The fascinating 'Neubaufahrzeug' in 1:35 from Amusing Hobby COVER STORY 28 SEVEN WHEELS ON MY WAGON Dragon's 1:35 Sd.Kfz.234 Pakwagen, minus one wheel 36 21ST CENTURY CENTURIO...
Magazines
Special Offers
£4.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
ADH Publishing MIM128
Military illustrated modeller#128 USUALLY é£6.70. NOW ON CLEARANCE!! HUGE SAVING!!! 4 NEWS Military modelling news 8 DIGGING OUT A PANZER IN BERLIN An evocative post-war scene in Berlin in 1:35 18 PREPARING THE HEULENDE KUH Sd.Kfz.251/1 Ausf.C 'Wurfrahmen 40' 20 "HERR LOUIS ZIMMER PRESENTS HIS MAGNETIC-MINE RESISTANT COATING " Zimmerit is applied to Dragon's 1:35 Sturmpanzer IV 26 WHICH WAY TO TURN? GIs in Sain...
Magazines
Special Offers
£1.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
ADH Publishing MIM129
Pacific Harasser Issue 129 WAS é£6.75. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS What's happening in modelling and aviation 6 REFERENCE P-40N Warhawk Close-Up 16 PREVIEW Arma Hobby 1:72 Mustang Mk.III COVER STORY 18 PACIFIC HARASSER Part One Trumpeter's 1:32 P-40M Warhawk by Brett Green 34 TORA! TORA! TORA! Part Two Eduard's 1:48 A6M2 Zero by Josef Blazek 44 KOOKABURRA OVER THE CONTINENT Mark Casiglia builds Has...
Magazines
Special Offers
£4.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
ADH Publishing MIM130
Military illustrated modeller#130 USUALLY é£6.70. NOW ON CLEARANCE!! HUGE SAVING!!! 4 NEWS Military scale-modelling news 8 ROLLING THROUGH THE RASPUTITSA Dragon's 1:35 SU-76i, a mud-coated machine on the Eastern Front 18 ABANDONED ARTILLERY IN HAUT-MESNIL Canadian soldiers examine Axis artillery 20 THEY WERE VERY CUMBERSOME THINGS AT FIRST Takom's excellent 1:35 Mk.IV 'Male' WW1 tank, expertly weathered 26 THE ...
Magazines
Special Offers
£1.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
ADH Publishing MIM132
Military illustrated Modeller issue number 132 WAS é£6.95. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS Military scale-modelling news COVER STORY 8 ENGIN BLINDƒÆ'aâé-° DE RECONNAISSANCE Hobbyboss's 1:35 EBR-11 French armoured car rolls in 16 SHOOTING TIGERS A KO'd King Tiger 18 SMALL, BUT PERFECTLY FORMED A 3D-Printed Polish light tank in 1:48 24 TANK, CRUISER, COMET I (A34) Useful images for Comet tank modelling projects ...
Magazines
Special Offers
£4.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

No Image
ADH Publishing MIM133
Military illustrated Modeller WAS é£6.95. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS What's happening in modelling and aviation 6 PREVIEW Eduard 1:48 F4F-3 Wildcat 8 PREVIEW Eduard 1:48 F4F-4 BRASSIN Cockpit 10 REFERENCE Fi-104 Flying Bomb 14 SEMI-KAMIKAZE Brett Green builds Bronco's 1:32 Fi-104 RE-4 piloted flying bomb. 18 PREVIEW Eduard 1:72 CS-199 Two Seater 26 DESERT STORM VIPER Tamiya's 1:48 scale F-16C by Grego...
Magazines
Special Offers
£4.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

No Image
ADH Publishing MIM134
No Scale
Military Illustrated Modeller WAS é£6.95. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS Military modelling news 8 WINTER OF DISCONTENT A chilly-looking 1:35 street scene with the Alan Hobby Pz.II ausf.J 16 ICE COLD IN AIRFIX Airfix's Austin K2/Y ambulance, previewed 18 FREEDOM'S EMBRACE Daniel Buchmeier constructs a highly evocative Holocaust scene in 1:35 26 LATE-WAR LEVELLER Tamiya's all new WW2 British A34 Comet Cruiser...
Magazines
Special Offers
£4.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

No Image
ADH Publishing MIM136
No Scale
Military Illustrated Modeller (issue 136) WAS é£6.95. BEING CLEARED!! SAVE 1/3RD!!! If War Comes Tomorrow 4 NEWS Military scale-modelling news 8 SCALE MODELWORLD 2022 A round-up of military models that caught the eye at Scale Modelworld 2022 16 DER STRASSENPANZERWAGEN! Copper State Models have created a fabulous 1:35 kit of the Ehrhardt Strassenwagen 1917 18 TRICKY DICKY'S BATTLEFIELD TAXI Lifecolor's new p...
Magazines
Special Offers
£4.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

No Image
ADH Publishing MIM139
No Scale
Military Illustrated Modeller (issue 139) WAS é£6.95. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS New release announcements 6 DUSTY Dassault-Mirage Kinetic 1:48 Dassault-Mirage 2000D by Jakub Chlodek 20 PREVIEW Aerocraft 1:32 Messerschmitt Me 209 V-1 22 KAWASAKI'S EAGLE Pt. 2 Mark Casiglia builds Tamiya's 1:48 scale Kawasaki Ki-61Hien COVER STORY 36 EASTERN WESPEN Eduard's 1:48 Messerschmitt Bf-110 E by Brett Green ...
Magazines
Special Offers
£4.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

No Image
ADH Publishing MIM141
Military Illustrated Modeller (issue 141) WAS é£6.95. BEING CLEARED!! SAVE 1/3RD!!! 4 NEWS New release announcements 6 REFERENCE Vampire FB.31 Close-Up by Brett Green 12 PREVIEW Kinetic 1:48 F-16D 14 WHO YOU CALLIN' ELEPHANT EARS? Airfix 1:48 de Havilland Vampire FB.31 Conversion by Brett Green Cover story 32 PENINSULA PONY Pt. 1 Mark Casiglia builds Tamiya's 1:32 F-51D Mustang. 44 BAVARIAN BLUE Wingnut Win...
Magazines
Special Offers
£4.60
-
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