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=299437 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

Mushroom Model Publications MMP8121
Macchi C.205 'Veltro' This detailed monograph covers the design and development of the Macchi C.205 Veltro (Italian for Greyhound), the main WWII production variant of this classic Italian fighter aircraft built by the Aeronautica Macchi. While the C.205 was able to match the best Allied opponents in speed and manoeuvrability, it was introduced late in the conflict. Owing to the limitations of the Italian war economy, only a s...
Aircraft books
Catalogue
£20.00
-
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

No Image
Mushroom Model Publications MMPCAM27
SCHWERER GELƒÆ'aâé-zNDEGƒÆ'aâé-zRGIGER PERSONENKRAFTWAGEN AND SUCCESSORS Camera On series #27 Alan Ranger This latest photo-album in the "Camera On" series contains 140 photographs of a broad selection of Germany's heavy off-road passenger vehicles in operation with the Wehrmacht during World War II. It includes both the Schwerer Gelƒ€ndegƒ€ngiger Personenkraftwagen and its successors, the Horch 108 type 1 and its licensed-built...
Aircraft books
Catalogue
£15.00
-
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

No Image
Mushroom Model Publications MMPMV01
SINGLE VEHICLE NO.1 SU-76M Skulski, Przemyslaw. Compilation of colour profiles, scale plans and photographic details of the single variant of the Su-76M Soviet self-propelled gun.
Military vehicle books
Catalogue
£9.00
-
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

Mushroom Model Publications MMPCAM17
Sd.Kfz.7 Mittlerer Zugkraftwagen 8t -Volume 2 by Alan Ranger This new photo album is number 17 in the MMPBooks/Stratus "Camera On" series and is the second volume on the Sd.Kfz. 7, the book contains 140+ photographs of the Sd. Kfz 7 and its various sub-types, the photos mostly unpublished before now, are all from German sources, with the majority being private photos taken by ordinary German soldiers not professional propag...
Military vehicle books
Catalogue
£15.00
-
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

Mushroom Model Publications MMP24-5
Warpaint volume 4 Colours and Markings of British Army Vehicles 1903-2003 by Dick Taylor. 978 83361421 24 5
Military vehicle books
Catalogue
£25.00
-
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

No Image
Mushroom Model Publications MMPCAM26
RENAULT R35 & R40 THROUGH A GERMAN LENS Camera On series #26 Alan Ranger The Renault R35, an abbreviation of Char lĩger Modele 1935 R (or R35), was a French light infantry tank of the Second World War. Designed from 1933 and produced from 1936, the tank was intended as an infantry support light tank. To this end it was relatively well-armoured but slow and lacking a good anti-tank capacity, fitted only with a short 37 m...
Aircraft books
Catalogue
£15.00
-
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

No Image
Mushroom Model Publications MMPSIN29
Single No.29 Yakovlev Yak-23
Aircraft books (on modelling aircraft)
Catalogue
£9.00
-
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

Mushroom Model Publications MMPCAM09
German Staff Cars WWII VOL. 1
Military vehicle books
Catalogue
£15.00
-
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
Mushroom Model Publications MMPSPOT23
McDonnell F-15 Eagle (Spotlight On No.23).
Aircraft books
Catalogue
£19.00
-
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
Mushroom Model Publications MMPCAM23
Staff cars in Germany WWII volume 2 Format A4 paperback, 80 pages This latest photo-album in the ""Camera On"" series contains 150 more photographs of private cars adapted for military use by the German forces during WWII. The remainder of the Opel motor company major production types are covered - the car types that were not covered in volume 1 - such as the Opel P-4 that was also used as the basis of many of the pre-war moc...
Aircraft books
Catalogue
£15.00
-
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

No Image
Mushroom Model Publications MMP57-0
Yugoslav Fighter Colours volume 1 1918 - 1941
Aircraft books
Catalogue
£35.00
-
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

No Image
Mushroom Model Publications MMPSIN21
No Scale
SINGLE NO.21 Mitsubishi A6M5 Model 52 Zero
Aircraft books (on modelling aircraft)
Catalogue
£9.00
-
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

No Image
Mushroom Model Publications MMPSIN18
SINGLE NO.18 Lockheed P-38G-15-LO Lightning Artur Juszczak & Dariusz Karnas Format A4, 24 pages (4 in colour) This book includes compilations of the 4-view colour profile, scale plans and photo details of the single variant of the Lockheed P-38G-15-LO Lightning. Scale plans in 1/72 and 1/48 scales plus drawings from wartime technical manuals. Also photos of the details in B&W and colour.
Aircraft books (on modelling aircraft)
Catalogue
£9.00
-
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

Mushroom Model Publications MMPCAM20
No Scale
CAMERA ON 20 3.7 FLAK 18/36/37 Authors Alan Ranger Format A4, 80 pages This latest photo album in the ""Camera On"" series is the first to cover an anti-aircraft weapon alone and whilst there have been a few books previously covering medium German 37mm Flak weapons, the 37 mm Flak 18/36/37 has never been covered in such photographic depth. This truly historically significant World War II weapon (along with the other Ger...
Aircraft books
Catalogue
£15.00
-
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

Mushroom Model Publications MMP9137
Bulgarian Fighter Colours 1919-1948. White Series - Volume 2 - Denes Bernad.
Aircraft books
Catalogue
£40.00
-
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

Mushroom Model Publications MMPCAM19
No Scale
WEHRMACHT IN THE MUD (Camera On No.19) - Alan Ranger Format A4, 80 pages This new World War II photo album is the first ever book to show such a broad selection of both equipment and vehicles that have one thing in common - the battle against mud. It is estimated that nearly as many German military vehicles that were destroyed by enemy action were also lost to muddy conditions. This book contains 140 photographs of Germa...
Aircraft books
Catalogue
£15.00
-
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

Mushroom Model Publications MMPSIN07
No Scale
SINGLE NO.07 DOUGLAS SBD-1 DAUNTLESS This book is compilations of the 4-view colour profile, scale plans and photo details of the single variant of the Douglas SBD-1 Dauntless. Scale plans in 1/72 and 1/48 scales plus drawings from wartime technical manuals. Also photos of the details in B&W and colour.
Aircraft books (on modelling aircraft)
Catalogue
£9.00
-
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

Mushroom Model Publications MMPSIN09
No Scale
SINGLE NO.09 PZL P.11A This book is compilations of the 4-view color profile, scale plans and photo details of the single variant of the PZL P.11a. Scale plans in 1/72 and 1/48 scales plus drawings from wartime technical manuals. Also photos of the details in B&W and color
Aircraft books (on modelling aircraft)
Catalogue
£9.00
-
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

Mushroom Model Publications MMP6143
North-American P-51B/P-51C & F-6C Mustang (Yellow Series) - Robert Pƒ"a"ączkowski
Aircraft books
Catalogue
£25.00
-
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

Mushroom Model Publications MMPCAM12
No Scale
15cm.s FH.18 heavy howitzer 'Camera On' series by Alan Ranger
Military vehicle books
Catalogue
£15.00
-
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

Mushroom Model Publications MMP3108
No Scale
The Forgotten War Of The Royal Navy Baltic Sea 1918-1920. This book covers the little known period of naval warfare in the Gulf of Finland, sparked by the German intervention in Finland and Estonia in early 1918 and evacuation of the Baltic Fleet through ice to Kronstadt. Through the entire year 1918 the Soviet forces were preparing their bases in Kronstadt and Petrograd. The attack of the German fleet was expected, but ...
Ship books (on modelling)
Catalogue
£25.00
-
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

Mushroom Model Publications MMPCAM01
No Scale
Sd.Kfz.6 Mittlerer Zugkfraftwagen 5t by Alan Ranger During WW2, the German armed forces were the greatest user of half-track vehicles. Such vehicles were used in the Wehrmacht, Luftwaffe and Waffen-SS, where they served as personnel carriers, tractors, combat engineering vehicles, as well as self-propelled carriage of anti-aircraft and anti-tank guns. One of them was the Sd.Kfz.6 (Sonderkraftfahrzeug 6) Mittlerer Zugkraf...
Military vehicle books
Catalogue
£15.00
-
In Stock
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