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 pt.id=3073
		 AND P.product_category_id = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



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

Seaforth Publishing SFP539-3
No Scale
Italian Naval Camouflage of World War II 9781526735393 Hardback by Marco GhiglinoPublished: July 2018
Ship books
Limited Availability
£35.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

Start UBF06
U-Boot im Focus Edition No 6 aâé-¢ 50 pages, 58 photos - thereof 3 colour-photos, 3 colour-conning tower sideviews, 1 coloured emblem, 1 coulered document, Readerforum aâé-¢ U-Boat in focus: U-404 The "Viking Ship" on a Patrol with an Underwater Camera aâé-¢ Unusual: U-178 with Wheels! aâé-¢ Photos with a Story: The Forgotten Negatives The Only Photos of U-215 aâé-¢ Conning Tower: U-702 The Boat with the Elephant's...
Ship books
Limited Availability
£17.80
-
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

Start UBF09
No Scale
U-Boot im Focus Edition No 9 ' 56 pages, 54 photos - thereof 12 in colour, 1 coloured sideview (U-509 Type IX), 4 coloured conning tower sideviews (U-509), 3 coloured maps, 1 coloured Emblem, Readerforum ' Boat in Focus: The Operations of 'Sea Wolf' Boat U-509 (Typ IX) ' Pennants: Success against PQ-14 (U-403) ' Unknown Emblems: The 3rd Submarine Flotilla's First Emblem ' Unusual: Awarding the War Merit Cross to U-6...
Ship books
Limited Availability
£17.80
-
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

Start UBF12
No Scale
U-Boot im Focus Edition No 12 ƒ¯é¿é½ 50 pages with 46 photos - thereof 4 in colour, 2 coloured documents, 1 coloured conning tower sideview, 2 coloured boat-sideviews, 4 coloured maps, 4 coloured emblems and Readers Forum ƒ¯é¿é½ Conning Towers: The Cape Buffalo Head Emblem on U-10's Conning Tower ƒ¯é¿é½ Color Photos: Late-War Color Photos of the 21st Flotilla (U-733) ƒ¯é¿é½ Boat in Focus: U-249's FuMB ...
Ship books
Limited Availability
£17.80
-
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

Start UBF17
No Scale
U-Boot im Focus 17 50 pages with 51 photos - thereof 1 in colour, 3 coloured emblems, 1 coloured document, 2 coloured conning tower frontviews, 4 coloured maps, Readers Forum aâé-¢ Commanders: Kptlt. Friedrich Guggenberger, Kptlt. Helmut Rosenbaum,Kptlt. Heinrich Schonder, Kptlt. Fritz Frauenheim,Kptlt. Werner Kraus, Oblt.(Ing.) Erich Zƒ¼rn aâé-¢ Reader's Forum: The Spanish Cross for SubmarinersU-393 The Experimental Boat of ...
Ship books
Limited Availability
£17.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

Squadron Signal SQS26010
USS Kidd (On Deck series)
Ship books
Limited Availability
£16.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

Squadron Signal SQS34006
USS Alabama at Sea (soft)
Ship books
Limited Availability
£16.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

Squadron Signal SQS34004
USS Saratoga (Squadron At Sea Series) Faced with the restrictions of the Washington Navy Treaty, the U.S. Navy had a choice: either scrap their partially complete, battlecruiser Saratoga, which along with her sister ship Lexington were intended to be the most powerful in the world; or convert them into an aircraft carriers. Wisely, the Navy chose the latter option and created what for many years would be the largest aircraft c...
Ship books
Limited Availability
£16.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

Squadron Signal SQS5604
USS Pampanito by Robert C. Stern (Walk Around Series)
Ship books
Limited Availability
£16.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

Squadron Signal SQS26007
USS Iowa Class Battleships On Deck. The Iowa-class battleships were America's and the world's last active battleships, serving as recently as 1995. Back in the World War II era, six of the class were ordered, and four completed: Iowa, New Jersey, Missouri and Wisconsin. The Missouri rose to fame as the site of the Japanese surrender ending WWII. Each armed with nine 16-inch rifles able to hurl 2,700-lb shells more than 23 mile...
Ship books
Limited Availability
£16.99
-
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