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=3080 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 426 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
No Image
Revell RV5179
Class 119 (Z1/Z5) German Destroyer
Ship kits, full hull (injection)
Catalogue
£145.99
-
In Stock
10+
Mach 2 MACHAR03
British L.C.A. landing craft
Ship kits, full hull (injection)
Catalogue
£18.99
Out of Stock
0
Mach 2 MACHAR07
P.B.R. Mk.II 'Pibber' U.S. Navy River Patrol Boat Vietnam
Ship kits, full hull (injection)
Catalogue
£25.99
-
In Stock
5+
No Image
Revell RV5175
1:72
PT-160 Patrol Torpedo Boat
Ship kits, full hull (injection)
Catalogue
£32.49
-
In Stock
10+
Trumpeter TU04521
1:350
Admiral Lazarev Soviet Cruiser (Ex-Frunze)
Ship kits, full hull (injection)
Catalogue
£74.99
Out of Stock
0
No Image
I LOVE KIT LK62007
1:200
USS Curtis Wilbur DDG-54 Guided Missile Destroyer 1994-present Fourth in the US Navy Arleigh Burke class of warships. Launched on May 16 1992 and commissioned on November 12 1993, it is named after Curtis D. Wilbur, the 43rd Secretary of the Navy. Single piece hull. Photo-etch parts, display stand & 1 x SH-60B included. L: 769mm, 111mm, Total parts 1300+
Ship kits, full hull (injection)
Catalogue
£172.99
£155.69
Out of Stock
0
No Image
Hobby Boss HB83420
R.M.S. Titanic
Ship kits, full hull (injection)
Catalogue
£48.99
£44.09
Out of Stock
0
No Image
Tamiya TA25426
Japanese Submarine I-400 Special Edition
Ship kits, full hull (injection)
Catalogue
£59.99
£53.99
Out of Stock
0
No Image
Trumpeter TU05374
1:350
Marseillaise 1937-42 French Light Cruiser
Ship kits, full hull (injection)
Catalogue
£92.99
£83.69
Out of Stock
0
No Image
Mikro-Mir MM35-029
Crusader K6 speedboat From Wikipedia.... Crusader was a jet-powered speed boat piloted by John Cobb. The combination of an aerodynamically stable hull form and turbojet propulsion was proposed by Reid Railton, Cobb's adviser. A rocket-powered scale model was tested at Haslar. The full size design was by Peter du Cane and built by Vospers of Portsmouth. Technical assistance came from Saunders-Roe and Vickers-Supermarine. It...
Ship kits, full hull (injection)
Catalogue
£43.20
£38.88
Out of Stock
0
No Image
Italeri IT5625
SCHNELLBOOT S-26/S-38
Ship kits, full hull (injection)
Catalogue
£189.99
-
In Stock
5+
Revell RV5096
1:350
Re-released! Tirpitz German Battleship (Due Oct 23)
Ship kits, full hull (injection)
Catalogue
£99.99
-
In Stock
7
No Image
Bronco Models KB14005
Ping Yuen Imperial Chinese Navy Peiyang Squadron
Ship kits, full hull (injection)
Catalogue
£24.99
-
In Stock
7
No Image
Mikro-Mir MM350-43
1:350
SSBN-611 John Marshall
Ship kits, full hull (injection)
Catalogue
£23.80
-
In Stock
5+
Zvezda ZVE9027
Re-issue! Soviet Battle Cruiser 'Borodino'
Ship kits, full hull (injection)
Catalogue
£34.99
-
In Stock
2
No Image
Aoshima AOS01103
I.J.N. BATTLE SHIP KIRISHIMA UPDATED EDITION
Ship kits, full hull (injection)
Catalogue
£145.00
-
In Stock
4
No Image
Airfix AX08252V
Great Western
Ship kits, full hull (injection)
Catalogue
£34.99
-
In Stock
2
No Image
Trumpeter TU06745
HMS York WWII Heavy Cruiser
Ship kits, full hull (injection)
Catalogue
£44.99
-
In Stock
5
No Image
Trumpeter TU06748
1:700
USS Missouri BB-63 WWII Battleship
Ship kits, full hull (injection)
Catalogue
£39.99
-
In Stock
5+
No Image
Mikro-Mir MM72-026
Italian CB Class Midget Submarines
Ship kits, full hull (injection)
Catalogue
£25.20
£22.68
Out of Stock
0
No Image
ICM ICMS019
Molch Midget Submarine U-Boat Type 'Molch', WWII German Midget Submarine (100% new molds) The Molch was a single-seat ultra-small submarine designed for operations in the coastal zone and belonged to the so-called special assault forces. During development, it was designated as "Thomas II", and production of serial models, known as "Molch", began in July 1944. The mini-submarine was equipped with a single electric motor for s...
Ship kits, full hull (injection)
Catalogue
£17.80
-
In Stock
5+
No Image
AFV Club AF70007
USS Oliver Hazard Perry Class Frigate (full hull) Lead ship of the 1970s Perry class of guided missile frigates. This is the full-hull version of the previously released waterline kit ref PKSE70006. With full hull and display base. Helicopter hangar with interior & open/close hangar gate; RQ-8B, OH58D, MQ-8C helicopters; RGM-84 harpoon & RIM-66; photo-etch mast; detailed AN/SPS-55 AN/SPS-49 radar; Mk 75 76mm/62 Mavy gun & Mk...
Ship kits, full hull (injection)
Catalogue
£44.99
-
In Stock
2
No Image
Trumpeter TU06747
Russian Destroyer Taszkient 1942
Ship kits, full hull (injection)
Catalogue
£28.99
-
In Stock
1
No Image
Hobby Boss HB86517
USS Iowa BB-61
Ship kits, full hull (injection)
Catalogue
£184.99
-
In Stock
3
Hobby Boss HB83513
USS Virginia SSN-774
Ship kits, full hull (injection)
Catalogue
£20.99
-
In Stock
1

   1   |   2   |   3   |   4   |   5      »      [18]
| +10

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