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_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 6,710 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

Scale

Product Type

Manufacturer

Added Within

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

Spin Models SPIN4806
1:48
Morane-Saulnier Type A1
Aircraft kits (resin)
Limited Availability
£38.50
-
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

Spin Models SPIN4809
1:48
Kondor E.3a
Aircraft kits (resin)
Limited Availability
£40.50
-
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

Spin Models SPIN4811
1:48
Wright Quadruplane
Aircraft kits (resin)
Limited Availability
£44.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

Start STARTDAS
Das vergessene As. Der Jagdfliger Gerhard Barkhorn by Bend Barbas The Forgotten Ace Fighter Pilot Gerhard Barkhorn Despite the fact that Gerhard Barkhorn scored a total of 301 victories while flying with Jagdgeschwader 52 on the Eastern Front, making him the second most successful fighter pilot of the Second World War after Erich Hartmann, today he is almost forgotten. This book describes Barkhorn's wartime experiences w...
Aircraft books
Limited Availability
£54.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

Silver Wings SVW32004
1:32
Focke-Wulf Fw-44D 'Stieglitz' on skis. Decals Luftwaffe. 130 resin parts (ski version) Paint masks Iron reinforced struts and landing gear Windscreen made of transparent resin Film for control panel http://www.silverwings.pl/132-focke-wulf-fw44-stieglitz.html
Aircraft kits (resin)
Limited Availability
£159.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

Silver Wings SVW32016
1:32
Curtiss P-6E Hawk. Kit includes: 108 resin parts, 29 p-e, film, reinforced wings & struts, instruction, decals Marking options: 3 US: P-6E 32-274, 17th Pursuit Squadron, 1st Pursuit Group, Selfridge Field, Michigan, 1932 US: P-6E 32-277, 94th Pursuit Squadron, Selfridge Field, Michigan, 1930 US: P-6E '22', 33rd Pursuit Squadron, 8th Pursuit Group, Virginia Beach Summer Camp, July 1934
Aircraft kits (resin)
Limited Availability
£159.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

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

Vingtor Decals VTH32-131
1:32
Lockheed-Martin F-35A Lightning II - RNoAF
Aircraft decals (military)
Limited Availability
£14.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
Vingtor Decals VTH48-154
North-American F-86F Sabre, Squadron codes & serials, RNoAF
Aircraft decals (military)
Limited Availability
£14.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

Vingtor Decals VTH72120
1:72
Supermarine Spitfire Mk.IXe - Norway 1945
Aircraft decals (military)
Limited Availability
£11.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
Bra.Z Models BZ4042
Vickers Super VC-10/VC10 Conversion inc. fuselage plugs, engines and wing leading edge (designed to be used with Airfix kits)
Aircraft conversions (resin)
Limited Availability
£18.40
-
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
Bra.Z Models BZ4120
Airport Terminal to Jetway Connecting Bridge Container Style
Diorama accessories (resin)
Limited Availability
£31.70
-
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
Bra.Z Models BZ4121
Airport Terminal to Jetway Connecting Bridge Glass Style
Diorama accessories (resin)
Limited Availability
£31.70
-
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

Bra.Z Models BZD413
1:144
Embraer ERJ-175 LOT "Retro" Silk-screen printed
Aircraft decals (civil)
Limited Availability
£12.60
-
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

Bra.Z Models BZG003
1:144
Cargo Trailer 3000kg
Diorama accessories (resin)
Limited Availability
£5.70
-
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

Bra.Z Models BZG004
1:144
LD-3 Container
Diorama accessories (resin)
Limited Availability
£3.80
-
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

Bra.Z Models BZG005
1:144
Pallet Container Transporter PCT 150-20
Diorama accessories (resin)
Limited Availability
£8.60
-
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

Bra.Z Models BZG006
1:144
LCD Container Trailer
Diorama accessories (resin)
Limited Availability
£6.20
-
In Stock
3

[1]   «      18   |   19   |   20   |   21   |   22      »      [269]
-10 | +10 +20 +50 +100

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