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=362691 AND ps.id = 955
		 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 27 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
Eduard kits EDK11158
ZERO ZERO ZERO! DUAL COMBO 1/48 Limited edition kit of Japanese WWII naval fighter plane A6M2 Zero Type 21 in 1/48 scale. The kit presents aircraft during their service in World War II. Plastic parts: Eduard kits, marking options: 12, decals: Eduard kits, PE parts: yes, pre-painted, painting mask: yes, resin parts: no (Eduard sold out March 2024 part 1)
Aircraft kits (injection)
Limited Availability
£59.99
-
In Stock
14

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 kits EDK8237
Mikoyan MiG-21PFM ProfiPACK
Aircraft kits (injection)
Limited Availability
£29.99
-
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
Eduard kits EDK11165
Panavia Tornado Limited edition Limited edition kit of European twin engine combat aircraft Tornado IDS designed for fighter bomber role in German Air Force in 1/48 scale. Kit presents not only German Tornados in markings from Tiger Meets but also in standard camouflage. - plastic part (Eduard sold out February 2024 part 1)
Aircraft kits (injection)
Limited Availability
£73.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
Eduard kits EDK82154
Supermarine Spitfire Mk.II Profipack edition kit of British fighter Spitfire Mk.IIb in 1/48 scale. - plastic parts: Eduard - marking options: 6 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes (Eduard sold out February 2024 part 1)
Aircraft kits (injection)
Limited Availability
£32.70
-
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

Eduard kits EDK82142
Focke-Wulf Fw-190A-4 Profipack edition of German fighter aircraft Fw-190A-4 in scale. The very first release of the kit. New design & moulds. - plastic parts: Eduard - No. of decal options: 5 - decals: Eduard - PE parts: yes, color - painting mask: yes - resin parts (Eduard sold out January 2024 part 2)
Aircraft kits (injection)
Limited Availability
£24.99
-
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

Eduard kits EDK8233
Mikoyan MiG-21SMT ProfiPACK edition of Soviet Cold War era jet aircraft MiG-21SMT in 1/48 scale. REEDITION - plastic parts: Eduard - No. of decal options: 6 - decals: Eduard - PE parts: yes, color - painting mask: yes - resin parts: no'
Aircraft kits (injection)
Limited Availability
£36.70
-
In Stock
18

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 kits EDK84157
Messerschmitt Bf-109E-3 Weekend edition kit of German WWII fighter aircraft - plastic parts: Eduard - marking options: 2 - decals: Eduard kits, - PE parts: no, - painting mask:no, (Eduard sold out July 2023 part 1)
Aircraft kits (injection)
Limited Availability
£17.99
-
In Stock
6

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 kits EDK82181
Zlin Z-126 Trener Profipack edition kit of Czechoslovak single engine two-seat trainer aircraft Zlin Z-126 in 1/48 scale. - plastic parts: Eduard - marking options: 6 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes - resin parts: no
Aircraft kits (injection)
Limited Availability
£26.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

Eduard kits EDK84128
Mikoyan MiG-21MFN Reedition of Eduard tool kit, decals printed by Eduard kits, 2 marking options. NO PE, NO Mask, NO resin included, full color instructions (Eduard sold out July 2023 part 1)
Aircraft kits (injection)
Limited Availability
£23.60
-
In Stock
11

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 kits EDK11144
1:48
ADLERANGRIFF 1/48 Limited edition DOUBLE kit of German WWII fighter Messerschmitt Bf-109E in 1/48 scale. Focused on version Bf-109E-1/3/4 from June to October 1940. - plastic parts: Eduard - marking options: 11 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes - resin (Eduard sold out May 2023 part 1)
Aircraft kits (injection)
Limited Availability
£51.30
-
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

No Image
Eduard kits EDK82124
Hawker Tempest Mk.II early version ProfiPACK edition kit of British fighter aircraft Tempest Mk.II in 1/48 scale. Focused on machine from the Royal Air Force. - plastic parts: Eduard - marking options: 6 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes - resin parts (Eduard sold out May 2023 part 1)
Aircraft kits (injection)
Limited Availability
£36.70
-
In Stock
15

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 kits EDK11148
WILDE SAU Episode Two: Saudammerung (Eduard sold out May 2023 part 2) 1/48 Limited edition kit of German WWII aircraft Messerschmitt Bf-109G-10 and Bf-109G-14/AS version in 1/48 scale. Focused on machines from JG 300 and JG 301. - plastic parts: Eduard - marking options: 11 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes
Aircraft kits (injection)
Limited Availability
£55.50
-
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
Eduard kits EDK11164
The Ultimate Hawker Tempest (Eduard sold out May 2023 part 2) Limited edition kit of British fighter aircraft Hawker Tempest Mk.II in 1/48 scale. Focused on machine from the Royal Air Force, Pakistan Air Force and Indian Air Force. - plastic parts:Eduard - Eduard marking : 10, - PE parts: yes, extra: book
Aircraft kits (injection)
Limited Availability
£59.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
Eduard kits EDK82139
Focke-Wulf Fw-190F-8 Profipack edition kit of WWII German fighter-bomber version of the Fw-190F-8 in 1/48 scale. - plastic parts: Eduard - marking options: 7 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes (Eduard sold out May 2023 part 2)
Aircraft kits (injection)
Limited Availability
£36.70
-
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

Eduard kits EDK82144
Focke-Wulf Fw-190A-3 ProfiPACK edition kit of German WWII fighter aircraft Fw-190A-3 in scale. The kit offers the aircraft with four-cannon wings. - plastic parts: Eduard - No. of decal options: 5 - decals: Eduard - PE parts: yes, pre-painted, - painting mask: yes (Eduard sold out May 2023 part 2)
Aircraft kits (injection)
Limited Availability
£29.70
-
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 kits EDK8262
Messerschmitt Bf-109E-3 ProfiPACK edition kit of German WWII fighter aircraft Bf-109E-3 in 1/48 scale. REEDITION, plastic parts: Eduard kits, No. of decal options: 5, decals: Eduard kits, PE parts: yes, color, painting mask: yes, resin parts: no (Eduard sold out May 2023 part 2)
Aircraft kits (injection)
Limited Availability
£23.60
-
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

Eduard kits EDK84127
BACK IN STOCK BUT NOW FINISHED!!! Mikoyan MiG-21PF Eduard plastic (2014 tool), decals printed by Eduard kits, 2 marking options. NO PE, NO Mask, NO resin included, full colour instructions (Eduard sold out May 2023 part 2)
Aircraft kits (injection)
Limited Availability
£23.60
-
In Stock
20+

   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