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=7892 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 42 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
Aims AIMS72D039
de Havilland Vampire F.3, FB.5 & FB.9 Collection' 1. F 3 VF327, 'A' 601 Sqn R Aux AF, 1952 2. F 3 VF327, 'G' 601 Sqn R Aux AF, 1952 3. F 3 VT809, 'Z' 73 Sqn, 1949 4. F 3 VV196, 'F' 32 Sqn, 1948 5. FB 5 VV628, 'C' 14 Sqn, 1951 6. FB 5 WA434, 'E' 603 Sqn R Aux AF, 1950 7. FB 5 WA331, 'A' 112 Sqn, 1951 8.FB 5 WG833, 'GM' Wg. Cdr. (F) Caledonian Wing, 1950- 9. FB 9 WL555, 'E' 60 Sqn, 1953 10. FB 9 WP996, 'D' 32 Sqn, 19...
Aircraft decals (military)
Catalogue
£14.40
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
Aims AIMS72D038
1:72
RFC/RAF roundels 1. Sopwith Pup/Royal_Aircraft_Factory Se.5 upper 2. Sopwith Pup/ Royal_Aircraft_Factory Se.5 upper/lower 3. Sopwith Pup side 3a. Sopwith Pup side 4. Sopwith Pup rudder 5. Sopwith Camel upper 6. Sopwith Camel lower 7. Sopwith Camel side 8. Sopwith Camel rudder 9. Sopwith Snipe upper 10. Sopwith Snipe lower 11. Sopwith Sopwith Snipe side 12. Sopwith Snipe rudder 13. Royal_Aircraft_Factory Se.5 side...
Aircraft decals (military)
Catalogue
£7.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

No Image
Aims AIMS72P011
PR Hawker Hurricane conversion for any kit (designed to be used with Airfix, Arma Hobby, Hasegawa, Hobby 2000, Heller and Revell kits)[Mk.I Mk.IIb Mk.IIc]
Aircraft conversions (resin)
Catalogue
£16.20
-
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
Aims AIMS72P014
Avro Lancaster Grand Slam wheels (designed to be used with Airfix kits)[Avro Lancaster B Mk.I Special 'Grand Slam']
Aircraft wheels (resin)
Catalogue
£10.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
Aims AIMS72D007
Re-printed! Heinkel He-111 Collection Part 2. (7) Heinkel He-111H-1 6N+IH 1/KG100 Norway 1940; A1+DA Stab/KG53 Legion Condor B of B 1940, both RLM 70/71/65; VG+ES North Africa RLM 78/79/80; A1+AH KG53 Russia RLM 71/72/65 with white areas; 1H+ML 3/KG26 Helsinki RLM 71/72/65; PG+PT KG27; Zwilling DS+EQ Test Programme
Aircraft decals (military)
Catalogue
£11.00
-
In Stock
20+

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
Aims AIMS72P013
1:72
Ju 88 late war long range fuel tank set 2 x 900 Ltr tank, 2x 600 Ltr tank, 2x LAST III ETC racks For Late war Long Range Ju 88 missions - perfect for 1/72 Hasegawa Junkers Ju-88S-1/3 (designed to be used with Hasegawa kits)
Aircraft detailing sets (resin)
Catalogue
£9.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
Aims AIMS72P009
Junkers Ju-88A-0 (designed to be used with Revell Ju-88A-1 kits)
Aircraft conversions (resin)
Catalogue
£9.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
Aims AIMS72P010
2x AB500 containers for any aircraft
Aircraft detailing sets (resin)
Catalogue
£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

No Image
Aims AIMS72P012
HK 19 German hand held camera x1 for any aircraft
Aircraft detailing sets (resin)
Catalogue
£1.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

No Image
Aims AIMS72D010
Re-printed! Heinkel He-111 collection Part 3. (8) B3+BL 1/KG54; +F KG55 with mission marks along fuselage, black undersurface, both Battle of Britain; 1H+BB Stab 1 KG26 Norway 1942; 1H+GP 8/KG26 Italy 1943; A1+AC II Gruppe Stab KG53 Russia 1943; NI+JE Scheppegruppe 4 Eastern Front overall white uppersurfaces; A1+KM 4/KG53 Russia 1944 RLM 76 `clouds', black undersurface; TM+KI glider tug Ukraine 1943; All standard RLM 70/71/76 ...
Aircraft decals (military)
Catalogue
£11.00
-
In Stock
8

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
Aims AIMS72D037
KG 66 20 x options! AN 8 sided A4 booklet! 6x Junkers Ju-88S-1 options 2x Junkers Ju-188E-1 options 1x Junkers Ju-88G-6 option 11x Junkers Ju-88S-3 options
Aircraft decals (military)
Catalogue
£16.00
-
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
Aims AIMS72PE02
Interior and Exterior combined PE details for the Italeri / Revell 1/72 Dornier Do-24 (limited availability ) (designed to be used with Italeri and Revell kits)
Aircraft detailing sets (etched)
Catalogue
£45.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

Aims AIMS72D027
1:72
Re-printed! Junkers Ju-88 Fighters' Junkers Ju-88G-1/Ju-88G-6/Ju-88C-2/Ju-88C-4/Ju-88C-6
Aircraft decals (military)
Catalogue
£13.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

No Image
Aims AIMS72P008
Dornier Do-217N-2 conversion (designed to be used with Italeri kits)
Aircraft conversions (resin)
Catalogue
£15.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
Aims AIMS72D036
1:72
JG 54 Messerschmitt Bf-109F-2 of the II. Gruppen Stab, flown by Lt. Paul Steindl. Eastern front, 1941. Messerschmitt Bf-109E-4, W Nr. 4093 of 9./JG 54 flown by either Lt. Waldemar Wƒ¼bke or Fw. Karl Kempf. Pancevo, 1941. Messerschmitt Bf-109F-4 W Nr. 13125 of 8./JG 54 flown by Oblt. Max-Hellmuth Ostermann Messerschmitt Bf-109F-2 of Stab JG 54, Flown by Technical Officer Oberleutnant Werner Pichon-Kalau vom Hofe. L...
Aircraft decals (military)
Catalogue
£8.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

Aims AIMS72D003
1:72
Re-printed! Felixstowe F.2A (1) N4283. Black shapes to complete option 1 in the Roden kit (expected first qtr 2022)
Aircraft decals (military)
Catalogue
£4.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

Aims AIMS72D004
Re-printed! Felixstowe F.2A (1) N4512. White squirles to complete option 2 in the Roden kit, N4512 21st June 1918 attempting to rescue Lt Packe. (expected first qtr 2022)
Aircraft decals (military)
Catalogue
£4.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

No Image
Aims AIMS72D034
Late War Messerschmitt Bf-109's Vol.2 Messerschmitt Bf-109G-14 Messerschmitt Bf-109G-5 Messerschmitt Bf-109G-6 Messerschmitt Bf-109G-10 Messerschmitt Bf-109K-4
Aircraft decals (military)
Catalogue
£8.50
-
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
Aims AIMS72P005
Messerschmitt Bf-110G-2/R-1 update or conversion for any Eduard Bf-110G (designed to be used with Eduard kits)
Aircraft conversions (resin)
Catalogue
£15.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
Aims AIMS72P006
RAF Camera set - includes 2x F8's, 2x F24's, 2x F52's, 2x Camera controls, 2x Camera selector boxes, 1x PE, 1x decal slip For WW II British Photo Reconnaissance aircraft
Aircraft conversions (resin)
Catalogue
£9.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
Aims AIMS72D035
Messerschmitt Bf-11G-2/R1
Aircraft decals (military)
Catalogue
£8.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

No Image
Aims AIMS72D033
Monotone Supermarine Spitfires Pr.Mk.IV, Pr Mk.IX, PR Mk.IG, Mk.Vc, Mk.Vb
Aircraft decals (military)
Catalogue
£8.50
-
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
Aims AIMS72F001
RAF/FAA pilot 1950's (set of two figure - one with flying helmet the other with FAA peaked cap
Figures (resin)
Catalogue
£8.00
-
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
Aims AIMS72F002
Luftwaffe Pilot - winter clothes
Figures (resin)
Catalogue
£4.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

Aims AIMS72P004
1:72
Junkers Ju-88A-1(F) / A-5(F) camera fairings and control box (designed to be used with Revell kits)
Aircraft conversions (resin)
Catalogue
£2.50
Out of Stock
0

   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