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 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 255 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

Aims AIMS32D015
'Late war Messerschmitt Bf-109's' Bf-109G-6 /AS White 7, 4 NJG 11, Lƒ¼bec, Germany Bf-109G-6 /AS Red 2, Flown by Friedrich-Karl Mƒ¼ller (30 victories), 1./NJGr. 10, Aug 1944 Bf-109G-14 /AS Black 2 Flown by Oblt Ernst Scheufele, 14./ JG 4. 3rd Dec 44, based at Frankfurt-Main, Germany Bf-109G-10 Erla Blue 2 Flown by Uffz Gerhard Reiher, 8./JG 3, Alperstadt, Germany, late 44 - early 45 Bf-109G-10 Erla Yellow 9 of II/JG 51...
Aircraft decals (military)
Catalogue
£10.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

No Image
Aims AIMS32PE20
Sopwith 2F.1 Camel 'Ships Camel' bracing wires (designed to be used with Wingnut Wings kits)
Aircraft detailing sets (etched)
Catalogue
£13.50
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 AIMS48D038
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, 195...
Aircraft decals (military)
Catalogue
£21.60
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 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 AIMS32D035
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
£22.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 AIMS32P049
de Havilland DH-100 Vampire Mk.3/Mk.5 'Block Tread' wheel set (designed to be used with Infinity Models kits)
Aircraft wheels (resin)
Catalogue
£12.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
Aims AIMS32P050
Hawker Hurricane early tail wheel fork (does not include tail wheel) 3D-Printed (designed to be used with Revell kits) Mk.I fork used also on some Mk.IIBs
Aircraft wheels (resin)
Catalogue
£7.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 AIMS48D037
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
£10.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 AIMS48P040
PR Hawker Hurricane conversion for any kit (designed to be used with Airfix, Arma Hobby, Hasegawa and Revell kits)[Mk.I Mk.IIb Mk.IIc]
Aircraft conversions (resin)
Catalogue
£18.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 AIMS48P049
de Havilland Vampire FB.5/FB.9 F.3 'Block Tread' wheel set (designed to be used with Airfix kits)
Aircraft wheels (resin)
Catalogue
£7.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 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 AIMS32P043
1:32
PR Hawker Hurricane conversion for Revell Mk.IIB kit but also with one option for a Mk.I kit and one option for a Mk.IIC kit (designed to be used with Revell kits)
Aircraft conversions (mixed-media)
Catalogue
£25.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 AIMS32P045
Cabane strut bracing wire clamps
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 AIMS32P046
CM/1 Rotol set - used on a few Mk.I Hawker Hurricanes and most Mk.IIA / Mk.IIB Hurricanes and some Mk.IIC (designed to be used with Revell kits)
Aircraft detailing sets (resin)
Catalogue
£7.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 AIMS32P047
ES/9 Rotol set - used on most Mk.IIC and Mk.IID and Mk.IV and also seen on a few converted PR Mk.II Hawker Hurricanes (designed to be used with Revell kits)[Mk.IIb]
Aircraft detailing sets (resin)
Catalogue
£7.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 AIMS32P048
Avro Lancaster Grand Slam wheels (designed to be used with Hong Kong Models kits)[Avro Lancaster B Mk.I Special 'Grand Slam']
Aircraft wheels (resin)
Catalogue
£18.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 AIMS48P045
Cabane strut bracing wire clamps
Aircraft detailing sets (resin)
Catalogue
£2.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
Aims AIMS48P048
1:48
Avro Lancaster Grand Slam wheels (designed to be used with Hong Kong Models kits)[Avro Lancaster B Mk.I Special 'Grand Slam']
Aircraft conversions (resin)
Catalogue
£13.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 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 AIMS32P042
AB 500 containers x2 for any German aircraft using them
Aircraft detailing sets (mixed-media)
Catalogue
£12.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

No Image
Aims AIMS32P044
Hk 19 German hand held camera 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 AIMS48P038
2x AB500 containers for any aircraft
Aircraft detailing sets (mixed-media)
Catalogue
£7.20
-
In Stock
5+

   1   |   2   |   3   |   4   |   5      »      [11]

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