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

1:72
Boeing B-29A Superfortress - wheels with radial tread (designed to be used with Academy and Airfix kits)
Aircraft wheels (resin)
Limited Availability
£12.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

1:72
Boeing B-17/B-24J/B-29A wheels late - diamond tread (designed to be used with Academy, Airfix and Hasegawa kits)
Aircraft wheels (resin)
Limited Availability
£12.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
1:144
Boeing P-8A Poseidon wheels set (weighted)
Aircraft wheels (resin)
Limited Availability
£6.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
Musketeers of the military household of the King of France (2018 revised set) 1 of the grey sprue and 2 of the cream coloured sprue.
Figures (injection)
Limited Availability
£14.99
-
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

1:72
Jumo 213 engine resin + PE
Aircraft engines and propellers (resin)
Limited Availability
£5.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
1:350
USS Constellation CV-64 PART II (designed to be used with Trumpeter kits) This BiG-Ed set includes all these Eduard sets..... ED53180 USN ensign flag modern STEEL 1/350 ED53233 Aircraft Carrier figures USN present 3D 1/350 ED53284 USS Constellation CV-64 part 4 - hull & deck 1/350 ED53285 USS Constellation CV-64 part 5 - island 1/350 (Eduard sold out February 2024 part 2)
Ship detailing sets (etched)
Limited Availability
£64.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

Boeing F/A-18E Super Hornet (designed to be used with Revell Kits) (Eduard sold out February 2024 part 2)
Aircraft detailing sets (etched)
Limited Availability
£14.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

1:48
Douglas TBD-1 'Devastator' exterior (designed to be used with Great Wall Hobby kits) (Eduard sold out February 2024 part 2)
Aircraft detailing sets (etched)
Limited Availability
£17.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

Sukhoi Su-17M3/M4 air brakes (designed to be used with Kitty Hawk Model kits) (Eduard sold out February 2024 part 2)
Aircraft detailing sets (etched)
Limited Availability
£11.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

McDonnell F-15C Eagle MSIP II seatbelts STEEL (designed to be used with Great Wall Hobby kits) (Eduard sold out February 2024 part 2)
Aircraft detailing sets (etched)
Limited Availability
£7.20
-
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
'Vietnam': A single 1/144 sheet (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch)
Diorama accessories (card)
Limited Availability
£4.20
-
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

1:144
Tarmac and Taxiway: A single 1/144 scale sheet approx. A4 size
Diorama accessories (card)
Limited Availability
£5.75
-
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
'WWII Luftwaffe Hardstand': A single 1/144 sheet (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch).
Diorama accessories (card)
Limited Availability
£4.20
-
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

1:144
Helipad: A new tarmac sheet depicting a military helicopter tarmac. This new state-of-the-art sheet depicts an actual modern military helicopter landing zone, and the terrain surroundings. It will fit various helicopter types and will therefore appeal to many enthusiasts. A single 1/144 sheet (approx. dimensions: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch).
Diorama accessories (card)
Limited Availability
£4.20
-
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
'Tarmac and Taxiway (No Lines)'. This a generic 1/144 scale sheet approx. A4 size. It complements your item #NM14406 but has the white lines removed, thus offering additional display options for the modeller.
Diorama accessories (card)
Limited Availability
£5.75
-
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

'Battle of Britain Airfield Set V.1 (Brick Wall) with Bonus 3D Component': Three 1/144 sheets (approx. dimensions of base and backdrop: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch). Version 1 includes 3 sheets: The first sheet depicts the concrete surface upon which the fighter parks; the second is a backdrop sheet depicting a brick revetment with the airfield view and a Supermarine Spitfire Mk.I and a Hawker Hurrica...
Diorama accessories (card)
Limited Availability
£6.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

1:144
"Large South East Asia Dispersal": This cardstock print depicts a significantly larger concrete parking area for 1/144 U.S. Vietnam war aircraft. Approx. dimensions: Length: 20 cm (7.8 inch); Width: 14 cm (5.5 inch)
Diorama accessories (card)
Limited Availability
£5.20
-
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:144
"Modern Fighter Hangar Set": Three 1/144 sheets as follows: Floor: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Rectangle Wall: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Square Wall: Length: 10.5 cm / 4.1 inch; Width: 10.5 cm / 4.1 inch. Quality printed on cardstock.
Diorama accessories (card)
Limited Availability
£12.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

1:144
"Soviet Fighter Hangar Set": Three 1/144 sheets as follows: Floor: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Rectangle Wall: Length: 14.8 cm / 5.8 inch; Width: 10.5 cm / 4.1 inch; Square Wall: Length: 10.5 cm / 4.1 inch; Width: 10.5 cm / 4.1 inch. Quality printed on cardstock.
Diorama accessories (card)
Limited Availability
£12.50
-
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
"IDF/AF HAS & Taxiway": A single 1/144 sheet as follows: Length: 21.5 cm / 8.47 inch; Width: 14.5 cm / 5.71 inch; Quality printed on cardstock
Diorama accessories (card)
Limited Availability
£5.20
-
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:144
"H.M.S. Glory Deck Korean War": A single 1/144 sheet. Length: 14.8 cm / 5.83 inch; Width: 10.5 cm / 4.13 inch. Ideal for models of the Hawker Sea Fury and the Fairey Firefly, serving on this ship during the Korean War period. Quality printed on cardstock.
Diorama accessories (card)
Limited Availability
£4.20
-
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 WWII Luftwaffe Hangar (Inside) Set": Six 1/144 sheets as follows: Floor Component I: 9.3 cm / 3.7 inch x 13.2 cm / 5.2 inch; Floor Component II: 18.6 cm / 7.3 inch x 13.2 cm / 5.2 inch; Wall Component I: 13.2 cm / 5.2 inch x 5.7 cm / 2.2 inch; Wall Component II: 9.3 cm / 3.7 inch x 5.7 cm / 2.2 inch; Wall Component III: 9.3 cm / 3.7 inch x 5.7 cm / 2.2 inch; Accessory Detail Set: 7.4 cm / 2.9 inch x 5.3 cm / 2.1 ...
Diorama accessories (card)
Limited Availability
£20.99
-
In Stock
2