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 = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 2,274 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

No Image
1:48
RBF/Remove Before Flight flags 4 night version Laser Carved Paper Sheet WAS é£4.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (paper)
Special Offers
£3.33
-
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
McDonnell F-101A/C Voodoo wheels (designed to be used with Hasegawa and Valom kits)[F-101C] WAS é£6.50. TEMPORARILY SAVE 1/3RD!!!
Aircraft wheels (resin)
Special Offers
£4.33
-
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
Henschel Hs-126A-1 "Superpava" Legion Condor WAS é£17.20. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£11.47
-
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
Lockheed F-104 Starfighter technical stencils DOUBLE SHEET [F-104C F-104J] WAS é£21.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£14.66
-
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
Lockheed F-80 USA & Europe Part 1 WAS é£19.99. TEMPORARILY SAVE 1/3RD!!! 1. Lockheed F-80B-1-LO Shooting Star 45-8598/ FN-598, flown by Col. Harry Russell Spicer, commander of the 36 th Fighter Group, Fƒ¼rstenfeldbruck Air Base, Germany, 1948. 2. Lockheed F-80A-5-LO Shooting Star 44-85464/FN-464 flown by Col. David Schilling, commander of the 56th Fighter Group, " Operation Fox Able l", Fƒ¼rstenfeldbruck Air Base, Germany,...
Aircraft decals (military)
Special Offers
£13.33
-
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
Lockheed F-80. USA & Europe Part 2 WAS é£15.99. TEMPORARILY SAVE 1/3RD!!! 1. Lockheed F-80C-10 Shooting Star 49-653/ FT-653, flown by Col. William T. Samways, commanding officer of the 8th Fighter Bomber Group, Itazuke AB, Japan, June 1950. 2. Lockheed F-80C-10 Shooting Star 49-665/FT-665, flown by Col Irwin H Dregne, commanding officer of the 51st Fighter Intercept Group, Tsuiki AB, Japan, July 1951. 3. Lockheed F-80C...
Aircraft decals (military)
Special Offers
£10.67
-
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

1:48
Aero L-29 'Delfin' nose nitrogen bay (designed to be used with Avante Garde kits) WAS é£6.99! NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£4.67
-
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

1:48
Agusta-Westland Merlin HC.3 engine set Né°1 (designed to be used with Airfix kits) WAS é£13.50! NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft engines and propellers (resin)
Special Offers
£8.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

Air intake and pitots for I-7U SUPERSONIC INTERCEPTOR PROTOTYPE. (designed to be used with Modelsvit kits) WAS é£19.20! NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (metal)
Special Offers
£12.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

1:32
Sukhoi Su-25 R-77 rocket details (designed to be used with Trumpeter kits) WAS é£6.80! NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (etched)
Special Offers
£4.53
-
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

Mikoyan MiG-19S/MiG-19PM Rocket start launcher (designed to be used with Trumpeter kits) WAS é£3.70! NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (brass)
Special Offers
£2.47
-
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

BetAb-500 Soviet Penetration bombs WAS é£6.60! NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£4.40
-
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

Red Grapes on green grapevines (fauna)(flora) WAS é£15.99 NOW BEING CLEARED!! SAVE 1/3RD!!! (Eduard sold out February 2022)
Diorama accessories (etched)
Special Offers
£10.67
-
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

Grapevine (white) colour (fauna) (Eduard sold out February 2020) WAS é£17.20. NOW BEING CLEARED!! SAVE é£7.21!!!
Diorama accessories (etched)
Special Offers
£9.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

Yakovlev Yak-18 SP-YYY decal + mask WAS é£6.80. LAST ONE BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£4.53
-
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

"WWI Airfield Set with Bonus Backdrop WAS é£8.70. LAST ONE BEING CLEARED!! SAVE 1/3RD!!! ": Three 1/144 sheets, including one FREE sheet (approx. dimensions of each sheet: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch)
Diorama accessories (card)
Special Offers
£5.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

1:144
"South East Asia (SEA) Helicopter Set". WAS é£7.99. LAST ONES BEING CLEARED!! SAVE 1/3RD!!! Two 1/144 sheets (approx. dimensions of base and backdrop: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch). The first sheet depicts the Marston mats (PSP) surface upon which the US military helicopter parked; the second is a backdrop sheet depicting a view of a typical US AFB in Vietnam with landscape elements, airplane and other...
Diorama accessories (card)
Special Offers
£5.33
-
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

Modern IAF HAS/Hardened Aircraft Shelter Tarmac: A single 1/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). WAS é£32.40. LAST ONES BEING CLEARED!! SAVE 1/3RD!!!
Bases and cases
Special Offers
£21.61
-
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

'Soviet PAG-14': A single 1/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). WAS é£32.40. LAST ONES BEING CLEARED!! SAVE 1/3RD!!!
Bases and cases
Special Offers
£21.61
-
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

1:32
"South East Asia (SEA) Helicopter Base". WAS é£32.40. LAST ONES BEING CLEARED!! SAVE 1/3RD!!! A single 1/32 sheet (approx. dimensions: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). Sheet depicts the Marston mats (PSP) surface upon which the U.S. military helicopter parked. Plotter-printed on heavy paper and shipped rolled in a sturdy carton tube.
Bases and cases
Special Offers
£21.61
-
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

"2 in 1: South East Asia (SEA) Helicopter Set". WAS é£48.50. LAST ONES BEING CLEARED!! SAVE 1/3RD!!! Two 1/32 sheets (approx. dimensions of base and backdrop: Length: 59.3 cm / 23.35 inch; Width: 40 cm / 15.75 inch). The first sheet depicts the Marston mats (PSP) surface upon which the U.S. military helicopter parked; the second is a backdrop sheet depicting a view of a typical U.S. AFB in Vietnam with landscape elements, airp...
Bases and cases
Special Offers
£48.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

"Soviet Bomber": WAS é£48.40. LAST ONE BEING CLEARED!! SAVE 1/3RD!!! 2 halves, separated along the demarcation lines, composing a single HUGE 1/72 sheet (approx. dimensions: Length: 1110 mm / 43.7 inch; Width: 780 mm / 30.71 inch). Shipped rolled in a sturdy carton tube. The print represents the complex elements that compose the concrete and asphalt surface upon which large Soviet bombers roll to and from the runway. Creat...
Bases and cases
Special Offers
£32.28
-
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

152mm ShkH vz.77 DANA tool boxes (designed to be used with Hobby Boss kits) WAS é£15.99. NOW BEING CLEARED! SAVE 1/3RD!!! (Eduard sold out December 2022)
Military vehicle detailing sets (etched)
Special Offers
£9.98
-
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

Supermarine Spitfire Mk.IX empty shell case chute (designed to be used with Hasegawa kits) WAS é£4.80. TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£3.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
Kamov Ka-10 WAS é£26.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£17.99
-
In Stock
2