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 pt.id=3014
		 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 47 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 113
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
NH Detail NHM72012
AS-42 Sahariana Detail Set (designed to be used with Italeri kits) WAS é£8.60. TEMPORARILY SAVE 1/3RD!!! The model 42 Desert Vehicle was developed for the Italian Army based on the chassis and mechanics of the AB 41 armoured car. Several dozen of these vehicles were produced. After the fall of the African front, the remaining AS 42s were re-deployed in Italy, ending their career in the police force in the 1950s.
Military vehicle detailing sets (etched)
Special Offers
£5.74
-
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

Airwaves (etched) AEC35034
Pz.Kpfw.III Turret Armour (designed to be used with Dragon kits) . WAS é£7.14. NOW BEING CLEARED! NOW SAVE MORE THAN 2/3RDS!!!![3987:18]
Military vehicle detailing sets (etched)
Special Offers
£1.99
-
In Stock
10+

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

Eduard ED36387
Pz.Kpfw.VI King Tiger initial zimmerit (designed to be used with Takom kits) USUALLY é£32.70. TEMPORARILY SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£21.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

Eduard ED36391
Sd.Kfz.171/267 Panther A late zimmerit (designed to be used with Takom kits) USUALLY é£36.70. TEMPORARILY SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£24.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

Eduard ED36424
1:35
Soviet SU-85 Soviet SPG (designed to be used with Zvezda kits) USUALLY é£18.80. TEMPORARILY SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£12.54
-
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
Eduard ED36496
Jagdpanther Ausf.G1 (designed to be used with Academy kits) USUALLY é£18.80. TEMPORARILY SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£12.54
-
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
Eduard ED36497
Jagdpanther Ausf.G1 Schurzen/Schƒ¼rzen (designed to be used with Academy kits) USUALLY é£23.60. TEMPORARILY SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£15.73
-
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
Eduard ED36499
Sd.Kfz.251/1 Ausf.C (designed to be used with Academy kits) USUALLY é£20.30. TEMPORARILY SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£13.53
-
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
Eduard ED36500
Sd.Kfz.251/1 Ausf.C side stowage bins (designed to be used with Academy kits) USUALLY é£18.80. TEMPORARILY SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£12.53
-
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

Aber ABR35G23
Grilles for Geschutzwagen Tiger Fur 17cm K72 (designed to be used with Trumpeter kits) WAS é£6.70. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£4.47
-
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

Eduard Big-Ed EBIG3594
Sd.Kfz.182 King Tiger with Porsche Turret (designed to be used with Meng Model kits) This BiG-Ed set includes all these Eduard sets... ED36398 King Tiger Porsche turret ED36399 King Tiger Porsche fenders ED36400 King Tiger Porsche zimmerit WAS é£57.20. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£38.14
-
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

Airwaves (etched) AEC35009
Soviet T-72MI (designed to be used with Dragon kits) WAS é£6.30. NOW BEING CLEARED!! SAVE 1/3RD!!! [4356:18]
Military vehicle detailing sets (etched)
Special Offers
£4.20
-
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

Eduard ED35987
1:35
Pz.Kpfw.IV Ausf.C (designed to be used with Dragon kits) WAS é£13.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£9.32
-
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

Eduard ED36061
1:35
M1127 Stryker Reconnaissance Vehicle (designed to be used with Trumpeter kits) WAS é£22.60. TEMPORARILY SAVE 1/3RD!!! (Eduard sold out December 2021)
Military vehicle detailing sets (etched)
Special Offers
£14.98
-
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

Eduard ED36076
APC M1134 ATGM (designed to be used with Trumpeter kits) WAS é£22.80. NOW BEING CLEARED! SILLY PRICE!!!
Military vehicle detailing sets (etched)
Special Offers
£4.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

Eduard ED36093
APC M1134 ATGM Blast panels (designed to be used with Trumpeter kits) WAS é£16.30. NOW BEING CLEARED!! SAVE MORE THAN 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£5.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

Eduard ED36128
1:35
M1127 additional armour (designed to be used with Trumpeter kits) . WAS é£19.50. NOW TEMPORARILY SAVE 1/3RD!!! (Eduard sold out December 2021)
Military vehicle detailing sets (etched)
Special Offers
£12.98
-
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

Eduard ED36187
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

Eduard ED36230
SPH M109A2 SPH (designed to be used with Kinetic Model kits) WAS é£17.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£11.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

Eduard ED36250
1:35
Benz typ G4 (1939) staff car tire/tyre chains (designed to be used with ICM kits) WAS é£12.99. TEMPORARILY SAVE 1/3RD!!! (Eduard sold out December 2021)
Military vehicle detailing sets (etched)
Special Offers
£8.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

Eduard ED36258
Merkava Mk.IV LIC armour shields (designed to be used with Academy kits) WAS é£16.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£10.93
-
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

Eduard ED36344
Soviet SU-76M fenders (designed for use with Tamiya kits) WAS é£21.30. NOW BEING CLEARED!! SAVE 1/3RD!!! (Eduard sold out June 2022)
Military vehicle detailing sets (etched)
Special Offers
£14.20
-
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

Eduard ED36356
Pz.Kpfw.IV Ausf.H (designed to be used with Zvezda kits) WAS é£16.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (etched)
Special Offers
£10.93
-
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

Eduard ED36398
1:35
Sd.Kfz.182 King Tiger Porsche turret (designed to be used with Meng Model kits) WAS é£15.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Military vehicle detailing sets (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

Eduard ED36421
1:35
US ensign flag WWII STEEL WAS é£17.20. NOW BEING CLEARED!! HALF PRICE!!!
Military vehicle detailing sets (etched)
Special Offers
£8.60
-
In Stock
2

   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