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=362756 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 99 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
Metallic Details MDMDR7259
ACES II Ejection seats x 2 3D-Printed Use for aircraft models Fairchild A-10 Thunderbolt II, McDonnell F-15 Eagle, Lockheed-Martin F-16 Fighting Falcon, F-22 Raptor, F-117 Nighthawk, B-1 Lancer, WB-57, B-2 Spirit.
Aircraft seats (resin)
Catalogue
£4.40
£3.96
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
Metallic Details MDMDR7269
FlaK 30/38. 2 cm FlaK 30/38 - 20 mm automatic anti-aircraft guns developed by Rheinmetall/Mauser
Aircraft detailing sets (resin)
Catalogue
£9.40
£8.46
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
Metallic Details MDMDR7270
Ejection seat Martin-Baker Mk.7. Contains 3D-Printed and photoetched parts to build 2 seats. Use for aircraft models EWR VJ 101, Grumman A-6 Intruder, Grumman F-9 Cougar, Grumman F-14 Tomcat, Lockheed F-104 Starfighter, McDonnell Douglas F-4, Phantom II, Northrop F-5, Northrop Grumman EA-6B Prowler, Vought F-8 Crusader.
Aircraft seats (resin)
Catalogue
£4.60
£4.14
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
Metallic Details MDMDR7271
Ejection seat Martin-Baker GRU-7A. Set contains 3D-Printed and photoetched parts to build 2 seats. Use for aircraft models EWR VJ 101, Grumman A-6 Intruder, Grumman F-9 Cougar, Grumman F-14 Tomcat, Lockheed F-104 Starfighter, McDonnell Douglas F-4, Phantom II, Northrop F-5, Northrop Grumman EA-6B Prowler, Vought F-8 Crusader.
Aircraft seats (resin)
Catalogue
£4.60
£4.14
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
Metallic Details MDMDR7272
Ejection seat Martin-Baker Mk.10. Set contains 3D-Printed parts to build 2 seats. Use for aircraft models Aermacchi MB-339, Aermacchi S-211, AMX International AMX, Atlas Cheetah, BAE Hawk, British Aerospace Sea Harrier, CASA C-101, Chengdu JJ-5, Chengdu J-7, Guizhou JJ-7, Dassault/Dornier Alpha Jet, Dassault-Mirage 2000, Dassault Rafale, Embraer EMB 314 Super Tucano, FMA IA 63 Pampa, Fouga 90, Hongdu JL-8, IAI Kfir, IAR 99,...
Aircraft seats (resin)
Catalogue
£4.60
£4.14
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
Metallic Details MDMDR7273
1:72
Rockwell B-1B Lancer Ejection seats (designed to be used with Airfix, Monogram and Revell kits)
Aircraft seats (resin)
Catalogue
£8.60
£7.74
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
Metallic Details MDMDR7274
Pratt & Whitney R-2800 Set contains 3D-Printed parts to build 1 engine "Pratt & Whitney R-2800". Applications: Brewster XA-32, Breguet Deux-Ponts, Canadair CL-215, Canadair C-5 North Star, Consolidated TBY Sea Wolf, Convair 240, 340, and 440, Curtiss P-60, Curtiss XF15C, Curtiss C-46 Commando, Douglas A-26 Invader, Douglas DC-6, Fairchild C-82 Packet, Fairchild C-123 Provider, Grumman AF Guardian, Grumman F6F Hellcat, Grumm...
Aircraft engines and propellers (resin)
Catalogue
£12.35
£11.12
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
Metallic Details MDMDR7275
1:72
Sd.Ah.51 Trailer . "The Sonderanhƒ¤nger (1 achs.) (Sd. Ah. 51) - one axle special trailer 51 for the transportation of the 2 cm Flak 30 and 38 and for the appropriated ammunition and equipment box."
Military vehicle kits (resin)
Catalogue
£5.99
£5.39
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
Metallic Details MDMDR7276
FlaK 30/38 with Sd.Ah.51 Trailer for the transportation of the 2 cm Flak 30 and 38 and for the appropriated ammunition and equipment box."
Military vehicle kits (resin)
Catalogue
£14.80
£13.32
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
Metallic Details MDMDR7277
Lockheed SR-71 Blackbird Ejection seats (designed to be used with Academy, Italeri and Hasegawa kits)
Aircraft seats (resin)
Catalogue
£4.60
£4.14
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
Metallic Details MDMDR7278
Vickers 40 mm automatic gun
Ship detailing sets (resin)
Catalogue
£7.99
£7.19
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
Metallic Details MDMDR7279
Blackburn Buccaneer Ejection seats (designed to be used with Airfix kits)
Aircraft seats (resin)
Catalogue
£4.60
£4.14
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
Metallic Details MDMDR7280
Consolidated B-24/Boeing B-29 Superfortress Hamilton Standard Propellers. Set contains 3D-Printed parts for detailing of 4 pcs "Hamilton Standard Propellers" for aircraft models B-24 and B-29 in scale 1/72.
Aircraft engines and propellers (resin)
Catalogue
£7.99
£7.19
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
Metallic Details MDMDR7281
Boeing B-29 Superfortress Propellers set late type (designed to be used wirh Academy and Airfix kits)
Aircraft engines and propellers (resin)
Catalogue
£7.99
£7.19
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
Metallic Details MDMDR7265
Wright R-2600 Set contains 3D-Printed parts to build 1 engine "Wright R-2600 Twin Cyclone" in scale 1/72. Applications: Boeing 314 Clipper, Brewster SB2A Buccaneer, Curtiss SB2C Helldiver, Douglas A-20 Havoc, Douglas B-23 Dragon, Grumman F6F Hellcat, Grumman TBF Avenger, Liorĩ et Olivier LeO 451, Martin Baltimore, Martin PBM Mariner, Miles Monitor, North-American B-25 Mitchell, Vultee A-31 Vengeance.
Aircraft engines and propellers (resin)
Catalogue
£11.40
-
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
Metallic Details MDMDR7267
Wright R-1820 Cyclone Set contains 3D-Printed parts to build 1 engine Applications: Boeing B-17 Flying Fortress, Boeing 307, Brewster F2A, Curtiss AT-32-A Condor, Curtiss SBC-4 Helldiver, Curtiss P-36 Mohawk, Curtiss SC Seahawk, Curtiss-Wright CW-21, Douglas A-33, Douglas B-18, Douglas DC-2, Douglas DC-3 (DST, G-102 and G-202), Douglas Super DC-3, R4D-8 / C-117, Douglas DC-5, Douglas DF Wright SGR-1820G-2, Douglas SBD Daun...
Aircraft engines and propellers (resin)
Catalogue
£11.40
-
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
Metallic Details MDMDR7258
DRASH Shelter Transport Trailer 3D-Printed
Diorama accessories (resin)
Catalogue
£26.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

No Image
Metallic Details MDMDR7266
Grumman F6F-3 Hellcat Cowling type 1 3D-Printed (designed to be used with Eduard kits)[F6F-5]
Aircraft detailing sets (resin)
Catalogue
£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

No Image
Metallic Details MDMDR7268
Antonov An-28 wheels and landing gear 3D-Printed (designed to be used with A-Model kits)
Aircraft detailing sets (resin)
Catalogue
£11.40
-
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
Metallic Details MDMDR7260
Antonov An-14 Pchelka. Interior/Exterior (designed to the used with A-Model kits)
Aircraft detailing sets (resin)
Catalogue
£13.40
-
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
Metallic Details MDMDR7261
Antonov An-14 Pchelka engines (designed to the used with A-Model kits)
Aircraft detailing sets (resin)
Catalogue
£13.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
Metallic Details MDMDR7262
Antonov An-14 Pchelka landing gears (designed to the used with A-Model kits)
Aircraft detailing sets (resin)
Catalogue
£13.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
Metallic Details MDMDR7263
Pratt & Whitney R-1830
Aircraft engines and propellers (resin)
Catalogue
£11.40
-
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
Metallic Details MDMDR7264
Consolidated B-24D. Liberator cowlings with PW R-1830 Engines (designed to the used with Italeri kits)
Aircraft detailing sets (resin)
Catalogue
£46.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
Metallic Details MDMDR7254
DRASH C-series type 2C
Diorama accessories (resin)
Catalogue
£51.99
-
In Stock
1

   1   |   2   |   3   |   4      »      [4]

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