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=362476 AND ps.id = 953 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		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
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 20 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
Hong Kong Models HKM01E29
Boeing B-17F Flying Fortress 'Memphis Belle' https://www.hk-models.com/hkm/shop/wwii/1-32-scale/1-32-b-17f-flying-fortress-01e029/ The first mass produced variant of the US Air Force WWII 4-engined heavy bomber, and available for the first time as a separate kit in 1/32 scale. Both aircraft portrayed in the kit's markings undertook war bonds tours in 1943/44, raising morale back in the States. Markings for 2 USAF aircr...
Aircraft kits (injection)
Catalogue
£409.99
-
In Stock
30+

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
Hong Kong Models HKM01E38
Avro Lancaster B Mk.I Special 'Grand Slam' https://www.hk-models.com/hkm/shop/wwii/1-32-scale/1-32-avro-lancaster-b-mk.l-special-grand-slam-01e038/ In early 1945 the Lancaster was the only aircraft capable of carrying the 22,400lb "Grand Slam" earthquake bomb designed by Barnes Wallis, the largest conventional bomb of WWII. 32 Lancasters of type B.I (Spec) had the bomb bay doors removed to assist loading the massive bom...
Aircraft kits (injection)
Catalogue
£409.99
-
In Stock
30+

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
Hong Kong Models HKM01E044
Boeing B-17G Flying Fortress 'Rose of York' includes resin figure https://www.hk-models.com/hkm/shop/wwii/1-32-scale/b-17g-flying-fortress-rose-of-york-limited-edition/
Aircraft kits (injection)
Catalogue
£439.99
£395.99
Out of Stock
0

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

Hong Kong Models HKM01E09
1:32
Dornier Do-335A-10 2 seat trainer https://www.hk-models.com/hkm/trash-category/1-32-dornier-do-335-a-10-trainer-01e09/
Aircraft kits (injection)
Catalogue
£184.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

No Image
Hong Kong Models HKM01E39
Douglas A-20G Havoc over Europe. https://www.hk-models.com/hkm/shop/wwii/1-32-scale/a-20g-havoc-over-europe/
Aircraft kits (injection)
Catalogue
£239.99
£215.99
-
In Stock
30+

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
Hong Kong Models HKM01E36
North-American B-25J Mitchell 'Strafing Babes' https://www.hk-models.com/hkm/shop/wwii/1-32-scale/b-25j-mitchell-strafing-babes-01e036/ Versatile WWII US Air Force twin-engine medium bomber named in memory of US General William "Billy" Mitchell who'd advocated the USA's increased air power after WWI, and became known as the 'father of the US Air Force'. The "J" was the final variant, first operational in 1944 and produce...
Aircraft kits (injection)
Catalogue
£249.99
£224.99
-
In Stock
12

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
Hong Kong Models HKM01E37
North-American B-25H Mitchell Gunships over CBI https://www.hk-models.com/hkm/shop/wwii/1-32-scale/1-32-b-25h-mitchell-gunships-over-cbi-01e037/ China Burma India Theater of Operations in World War II Markings for 2 USAF aircraft. (1) s/n 43-4975, 81st BS/12th BG, India, Spring 1943; (2) s/n 43-4244, 81st BS/12th BG, India, Spring 1945. L: 498mm, W: 643mm, 563 parts.
Aircraft kits (injection)
Catalogue
£249.99
£224.99
-
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
Hong Kong Models HKM01E33
Avro Lancaster B Mk.I Nose Art Kit https://www.hk-models.com/hkm/shop/wwii/1-32-scale/1-32-avro-lancaster-b-mk.i-nose-art-kit-01e033/?cookies_accepted=Y
Aircraft kits (injection)
Catalogue
£79.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

Hong Kong Models HKM01E10
Avro Lancaster B Mk.I all our stock is the limited edition kit that includes EXTRA FUSELAGE AND NOSE SECTION in CLEAR PLASTIC giving the modeller the choice of building the Lancaster to show all of the incredible interior detail, or as a conventional plastic kit using the detailed and textured grey styrene fuselage, nose and all other parts. https://www.hk-models.com/hkm/shop/wwii/1-32-scale/avro-lancaster-b-mk.-1/ Mark...
Aircraft kits (injection)
Catalogue
£399.99
-
In Stock
27

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

Hong Kong Models HKM01E24
North-American B-25J Mitchell 'Glass Nose' https://www.hk-models.com/hkm/trash-category/1-32-b-25j-mitchell-glass-nose-over-mto-01e024/ over MTO Mediterranean Theatre of Operations, North Africa and Italy, WWII
Aircraft kits (injection)
Catalogue
£219.99
£197.99
Out of Stock
0

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

Hong Kong Models HKM01E11
Avro Lancaster B Mk.III Dambuster ED932/AJ-G https://www.hk-models.com/hkm/trash-category/1-32-avro-lancaster-dambuster-01e011/ aâé-¢ New tooled parts for Upkeep bouncing bomb used in Operation Chastise dams raid in May 1943. aâé-¢ Markings for Lancaster B Mk.III Special ED932/AJ-G of 617 Squadron RAF Scampton. aâé-¢ L: 664mm, W: 972mm, Total parts: 854. aâé-¢ Exclusive UK edition includes new booklet "Rad Shutters Auto" b...
Aircraft kits (injection)
Catalogue
£399.99
-
In Stock
7

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

Hong Kong Models HKM01E15
de Havilland Mosquito Mk.IV / PR.Mk.I/IV https://www.hk-models.com/hkm/shop/wwii/1-32-scale/1-32-de-havilland-mosquito-b-mk-iv-series-ii-01e15/ Brand new state of the art tooling. Wingspan 515mm, length 389mm. Stunning single piece wing. Optional bulged bomb bay & 'Cookie' bomb. Decals for three aircraft by Cartograf.
Aircraft kits (injection)
Catalogue
£184.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

Hong Kong Models HKM01E21
Dornier Do-335B-6 'Pfeil' Night Fighter https://www.hk-models.com/hkm/trash-category/1-32-dornier-do-335-b-6-night-fighter-01e021/
Aircraft kits (injection)
Catalogue
£174.99
£157.49
-
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

Hong Kong Models HKM01E30
Boeing B-17G Flying Fortress Late Production https://www.hk-models.com/hkm/shop/wwii/1-32-scale/b-17g-flying-fortress-late-production-01e030/
Aircraft kits (injection)
Catalogue
£399.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

Hong Kong Models HKM01E16
de Havilland Mosquito Mk.IX/XVI RAF Bomber https://www.hk-models.com/hkm/trash-category/1-32-de-havilland-mosquito-b-mk.ix-mk.xvi-01e16/
Aircraft kits (injection)
Catalogue
£184.99
£166.49
Out of Stock
0

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

Hong Kong Models HKM01E08
Dornier Do-335A https://www.hk-models.com/hkm/shop/wwii/1-32-scale/1-32-dornier-335-a-fighter-bomber-01e08/
Aircraft kits (injection)
Catalogue
£195.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

Hong Kong Models HKM01E07
Dornier Do-335B-2 'Zerstorer' https://www.hk-models.com/hkm/shop/wwii/1-32-scale/1-32-dornier-335b-2-zerstorer-01e07/ The Dornier Do-335 "Pfeil" ("Arrow") was the fastest piston-engined German Luftwaffe aircraft of WWII, and was also the only military aircraft of the time to feature a "push-pull" engine layout airframe. The B-Series "Zerstorer" ("Destroyer") was a heavily armed version intendedspecifically for the heavy ...
Aircraft kits (injection)
Catalogue
£195.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

Hong Kong Models HKM01E03
North-American B-25H Mitchell https://www.hk-models.com/hkm/trash-category/1-32-b-25h-mitchell-gunship-01e03/
Aircraft kits (injection)
Catalogue
£240.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

Hong Kong Models HKM01E06
Gloster Meteor F.4 https://www.hk-models.com/hkm/trash-category/1-32-gloster-meteor-f4-01e06/
Aircraft kits (injection)
Catalogue
£75.99
£68.39
-
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

Hong Kong Models HKM01E02
North-American B-25J Mitchell 'The Strafer'] https://www.hk-models.com/hkm/trash-category/1-32-b-25j-mitchell-the-strafer-01e02/
Aircraft kits (injection)
Catalogue
£240.99
£216.89
-
In Stock
3


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