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=362578 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 12 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

No Image
Takom TAK05011
FAUN SLT-56 Franziska & Leopard 2A7 FS 42.75/42 8 x 6 motive unit + Anh 56t 6-axle transporter semi-trailer of 56 tons capacity. German Army Leopard 2A7 Main Battle Tank. New tooling. SLT-56 L: 275.3mm, W: 42.7mm. 2A7 L: 152mm, W: 52.4mm
Military vehicle kits (injection)
Catalogue
£65.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
Takom TAK05017
German Silbervogel WWII concept Suborbital Bomber Silbervogel ("silver bird") was a proposed intercontinental military space bomber designed in the late 1930s by Austro-Hungarian rocket pioneer Eugen Sƒ¤nger and German mathematician Irene Bredt. The Third Reich's plans for a long-range strategic bomber with the capability of striking New York City from Germany were abandoned, fortunately, due to the combination of high cost,...
Aircraft kits (injection)
Catalogue
£44.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

Takom TAK05001
Re-issue! V-2 rocket + Meillerwagen transport + Hanomag SS-100. Launcher included aâé-¢ assemble in launch or transport position aâé-¢ PE parts.
Military vehicle kits (injection)
Catalogue
£44.99
£40.49
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
Takom TAK05022
1:72
The M60A1 was an upgraded version of the M60 main battle tank. Introduced in 1969 it featured a number of improvements including a new 105mm rifled gun, a thermal sleeve for the gun barrel and a new fire control system. The M60A3 was a further upgrade of the M60A1 introduced in 1978. Improvements included a 105mm smoothbore gun, a laser rangefinder and a new ballistic computer. Both tanks were used by the United States milit...
Military vehicle kits (injection)
Catalogue
£34.99
£31.49
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
Takom TAK05020
1:72
HMS Hood Mk 1 15"/42 Gun Turret B Royal Navy Admiral Class Battlecruiser built and engined in Clydebank by Messrs John Brown & Co Ltd. Laid down in September 1916 and launched in August 1918, HOOD was the largest warship afloat when commissioned on May 15th 1920, retaining that distinction for the next twenty years. HOOD was armed with eight 15in 42-calibre Mk I guns in four twin turrets in superfiring position, designated...
Gun kits (injection)
Catalogue
£51.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
Takom TAK05021
US M1070 & M1000 70 Ton Tank Transporter Present-day M1070 heavy-duty 8-wheel tractive unit and 5-axle multi-wheel low-loader gooseneck trailer. Bonus 3D-Printed radiator cover included. Photo-etch parts. Choice of 3 markings.
Military vehicle kits (injection)
Catalogue
£49.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
Takom TAK05019
US M1070 Gun Truck, 1990s to date Improvised escort combat vehicle with up-armoured cab and two rear-mounted guns. The M1070 heavy-duty 8-wheel tractive unit with front and rear steer is built by Oshkosh Corporation USA. Markings for 1 gun truck. Photo-etch parts included.
Military vehicle kits (injection)
Catalogue
£35.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

Takom TAK05002
M1070 & M1000 w/D9R US Army 70 Ton Tank Transporter & Bulldozer comprising M1070 8x8 Truck Tractor, M1000 Heavy Equipment Transport Semi-trailer, D9R Armoured Bulldozer. [Israeli]
Military vehicle kits (injection)
Catalogue
£79.99
-
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
Takom TAK05016
Scharnhorst Turret B, 28cm SK C/34 guns, WWII German Navy One of two superfiring forward turrets carried by the pride of the German Kriegsmarine. Parts for one turret. L: 303mm. Metal barrels, photo-etch and base included. Choice of 2 markings
Gun kits (injection)
Catalogue
£63.99
-
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
Takom TAK05018
German Silbervogel Suborbital Bomber & Atomic Payload Suite Proposed Third Reich intercontinental military space bomber that got no further than the drawing board. Includes Hanomag SS100 prime mover with 2 bomb trolleys plus nuclear bomb load. Optional single or two-seat cockpit, choice of 4 markings (different from PKTAK05017). Spaceplane L: 386mm.
Aircraft kits (injection)
Catalogue
£59.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

No Image
Takom TAK05015
USS Missouri Mk 7 16"/50 Gun Turret No 1 United States Navy three gun turret, main armament of the two WW2 Iowa Class battleships Missouri and Iowa. Parts for one turret. L: 403mm. Photo-etch and base included. Choice of 2 markings.
Gun kits (injection)
Catalogue
£66.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
Takom TAK05014
Bismarck Turret, 15cm SK C/28 Gun, Bb II/Stb II Medium-calibre naval guns of the WWII German battleship Bismarck. Parts for one turret, either Bb II (Backbordseite, port side) or Stb II (Steuerbordseite, starboard side). L: 160mm. Photo-etch and display base included.
Gun kits (injection)
Catalogue
£29.99
-
In Stock
1


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