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 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 36 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

No Image
Eduard kits EDK2143
WUNDERSCHONE NEUE MASCHINEN pt. 2 DUAL COMBO Limited First edition of new tool 1/72 Messerschmitt Bf-109G-2/G-4 The Limited edition of the kit of the famous German WWII fighter aircraft Messerschmitt Bf-109G in 1/72 scale. The kit offers aircraft of G-2 and G-4 versions. Marking selection covers all fronts of WWII where these Gustavs were fighting including airplanes from the Ilmavoimat (Finish Air Force), Italian Regia Aerona...
Aircraft kits (injection)
Limited Availability
£25.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 EDK4453
Avia B.534 IV. serie Super 44 edition kit of Czechoslovak fighter aircraft Avia B.534 IV. serie in 1/144 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: yes - resin parts: no
Aircraft kits (injection)
Limited Availability
£8.60
-
In Stock
17

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

Eduard kits EDK7434
Grumman F6F-5N Hellcat Nightfighter Eduard plastic, decals printed by Eduard kits, 2 marking options. NO PE, NO mask, NO resin included, full color instructions (Eduard sold out February 2024 part 1)
Aircraft kits (injection)
Limited Availability
£10.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

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

No Image
Eduard kits EDK7407
Fokker D.VII (OAW) 1/72 Weekend edition kit of German WWI fighter aircraft Fokker D.VII in 1/72 scale. The kit is focused on aircraft built by OAW factory. - plastic parts: Eduard - No. of decal options: 4 - decals: Eduard - PE parts: no - painting mask: no - resin part (Eduard sold out December 2023 part 1)
Aircraft kits (injection)
Limited Availability
£11.99
-
In Stock
17

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 EDK2139
GUNN's BUNNY 1/72 Limited edition kit of US WWII medium bomber North-American B-25J Mitchell with solid nose in 1/72 scale. Kit presents machines from the Pacific and China Burma India Theater. Plastic parts:Hasegawa - marking options: 10 - decals: Eduard - PE parts, mask, resin wheels (Eduard sold out November 2023 part 1)
Aircraft kits (injection)
Limited Availability
£59.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 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

No Image
Eduard kits EDK70153
Avia CS-199 Profipack edition kit of Czechoslovak single engine two-seat training fighter Avia CS-199 in 1/72 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
£17.50
-
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 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

Eduard kits EDK3006
1:32
Messerschmitt Bf-108 Taifun ProfiPACK edition kit of German WWII liasion aircraft Bf-108 in 1/32 scale. Plastic parts: Foke - marking options: 5 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes - resin parts: yes, wooden propeller (Eduard sold out May 2023 part 1)
Aircraft kits (injection)
Limited Availability
£38.30
-
In Stock
10+

   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