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=362473 AND ps.id = 958 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 45 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
Mk.1 Design MD-20024
1:200
DKM SCHARNHORST DX Pack German Gneisenau Battleship (designed to be used with Trumpeter kits) Composition : 20 X High Quality Brass Photo Etched Parts 1 X Pre-Painted Brass Photo Etched Parts 95 X Brass Metal Parts 161 X 3D-Printed High Quality Resin Parts 0.5mm/1.0mm Brass Rod Anchorchain Full Wooden Deck
Ship detailing sets (etched brass and wood)
Catalogue
£319.99
£287.99
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
Mk.1 Design MD-20025
DKM SCHARNHORST Value Pack German Gneisenau Battleship (designed to be used with Trumpeter kits) Composition : 15 X High Quality Brass Photo Etched Parts 1 X Pre-Painted Brass Photo Etched Parts 26 X Brass Metal Parts 46 X 3D-Printed High Quality Resin Parts 0.5mm Brass Rod Anchor Chain Full Wooden Deck
Ship detailing sets (etched brass and wood)
Catalogue
£219.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
Mk.1 Design MS-20019
DKM SCHARNHORST German Gneisenau Battleship (designed to be used with Trumpeter kits) DKM SCHARNHORST Armament Parts Composition : 2 X High Quality Brass Photo Etched Parts 51 X Brass Metal Parts 1.0mm Brass Rod 115 X 3D-Printed High Quality Resin Parts
Ship detailing sets (metal and resin)
Catalogue
£94.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
Mk.1 Design MS-20020
1:200
DKM SCHARNHORST German Gneisenau Battleship (designed to be used with Trumpeter kits) Radar & Mast Parts Composition : 3 X High Quality Brass Photo Etched Parts 18 X Brass Metal Parts
Ship detailing sets (etched and metal)
Catalogue
£59.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
Mk.1 Design MD-20021
USS CV-6 Enterprise DX (designed to be used with Trumpeter kits) 22 X High Quality Photo Etched Parts 1 X Pre-painted Photo Etched Part 54 X Turned Brass Metal Parts 246 X 3D-Printed High Quality Resin Parts (Include 3D-Printed High Quality Resin Figure (Designed by Tori Factory)) 5 X Cast Resin Parts
Ship detailing sets (etched, metal and resin)
Catalogue
£255.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
Mk.1 Design MD-20022
USS CV-6 Enterprise DX with Full Wooden Deck (designed to be used with Trumpeter kits)
Ship detailing sets (etched, metal and resin)
Catalogue
£310.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
Mk.1 Design MD-20023
USS CV-6 Enterprise Wooden Deck (designed to be used with Trumpeter kits) 7 X High Quality Photo Etched Parts. Full Wonden deck
Ship detailing sets (wood)
Catalogue
£110.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
Mk.1 Design MS-20018
USS WWII US DECK PLANE SET for CV-6 Enterprise (designed to be used with Trumpeter kits) suitable for other aircraft carriers 25 X Turned Metal Parts 2 X Photo Etched Parts 1 X Pre-painted color Photo Etched Part 9 X Resin Parts 150 X 3D-Printed High Quality Resin Parts
Ship detailing sets (resin)
Catalogue
£70.00
£63.00
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
Mk.1 Design MD-20020
1:200
R.M.S Titanic detail-up set (designed to be used with Trumpeter kits) 20 X High Quality Photo Etched Parts 1 X Pre-painted Photo Etched Part 122 X Brass Metal Parts 271 X 3D-Printed High Quality Resin Parts 25 X Cast Resin Parts 1 X Stained Glass Sheet (Clear Film) Full Wooden deck 180 X 3D-Printed High Quality Resin Figure (designed by Tori Factory)
Ship detailing sets (etched, metal and resin)
Catalogue
£330.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

Mk.1 Design MS-20013
H.M.S. RODNEY ARMAMENT SET (designed to be used with Trumpeter kits)
Ship detailing sets (brass)
Catalogue
£154.99
-
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

Mk.1 Design MS-20014
1:200
H.M.S. RODNEY MAST AND RADAR SET (designed to be used with Trumpeter kits)
Ship detailing sets (brass)
Catalogue
£39.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

Mk.1 Design MD-20018
1:200
Mikasa 1905 Japanese Battleship (designed to be used with Hobby Boss and Merit kits) VALUE PACK Wooden Deck & DX Pack
Ship detailing sets (etched brass and wood)
Catalogue
£204.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

Mk.1 Design MS-20011
1:200
H.M.S. Nelson 1944 mast and radar set (designed to be used with Trumpeter kits)
Ship detailing sets (brass)
Catalogue
£39.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

Mk.1 Design MD-20019
USS Arizona BB-39 DX.II (designed to be used with Trumpeter kits) Full Wooden Deck 13 X Brass Photo Etched Part 1 X Pre-Painted Stainless Steel Photo Etched Part 55 X Turned Metal Parts 20 X Resin Parts 5 X Color Manual (10 page)
Ship detailing sets (etched, metal and resin)
Catalogue
£284.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

Mk.1 Design MS-20017
1:200
USS Arizona BB-39 DX.II (designed to be used with Trumpeter kits) 55 X Turned Metal Parts 20 X Resin Parts 3 X Color Manual (6 page) *This product has the same composition and price as MD-20019 Arizona DX.II when you added as a product can be added to MD-20001 Arizona DX.
Ship detailing sets (brass)
Catalogue
£94.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

Mk.1 Design MS-20012
1:200
WWII U.S. DECK PLANE SET I for the aircraft in the USS HORNET CV-8 (designed to be used with Merit and Trumpeter kits)
Ship detailing sets (brass)
Catalogue
£74.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

Mk.1 Design MD-20017
1:200
Mikasa 1905 Japanese Battleship (designed to be used with Hobby Boss and Merit kits) DX * Large Sized Full Wooden Deck * 9 X Large Sized Detailed Brass Photo Etched Parts * 1 X Large Sized Pre-Painted Detailed Brass Photo Etched Parts
Ship detailing sets (etched brass and wood)
Catalogue
£284.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

Mk.1 Design MD-20015
1:200
H.M.S. HOOD DX PACK (designed to be used with Trumpeter kits) * Large Sized Full Wooden Deck * 21 X Large Sized Detailed Brass Photo Etched Parts * 2 X Pre-Painted Color Etched Parts * 157 X Turned Metal Parts * 48 X Resin Parts * 6 X White Metal Parts * 50Cm Chain Pictures are here http://www.ka-models.com/index.php?route=product/product&keyword=Hood&category_id=0&product_id=695 All Parts Made in Korea
Ship detailing sets (etched brass and wood)
Catalogue
£294.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

Mk.1 Design MD-20016
1:200
H.M.S. HOOD VALUE PACK (designed to be used with Trumpeter kits) * Large Sized Full Wooden Deck * 14 X Large Sized Detailed Brass Photo Etched Parts * 1 X Pre-Painted Color Etched Part * 56 X Turned Metal Parts * 6 X White Metal Parts * 25 X Resin Parts * 50Cm Chain All Parts Made in Korea
Ship detailing sets (etched brass and wood)
Catalogue
£204.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

Mk.1 Design MS-20015
1:200
H.M.S. HOOD ARMAMENT DETAILING SET (designed to be used with Trumpeter kits)
Ship detailing sets (brass)
Catalogue
£89.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

Mk.1 Design MS-20016
1:200
H.M.S. HOOD MAST & RADAR SET (designed to be used with Trumpeter kits)
Ship detailing sets (brass)
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

Mk.1 Design MD-20012
USS HORNET CV-8 (designed to be used with Merit and Trumpeter kits) WOODEN DECK
Ship detailing sets (wood)
Catalogue
£109.99
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

Mk.1 Design MD-20013
1:200
H.M.S. Rodney DX PACK (designed to be used with Trumpeter kits)
Ship detailing sets (wood)
Catalogue
£284.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

Mk.1 Design MD-20014
1:200
H.M.S. Rodney VALUE PACK (designed to be used with Trumpeter kits)
Ship detailing sets (etched brass and wood)
Catalogue
£149.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

Mk.1 Design MD-20010
1:200
H.M.S. Nelson 1944 DX PACK (designed to be used with Trumpeter kits)
Ship detailing sets (etched brass and wood)
Catalogue
£284.99
-
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