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=1812
		 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 32 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

Multicast MC-72001
Lockheed C-130J-30 Hercules Conversion Set USAF and IAF (designed to be used with Italeri kits) (ex Wingman with additional parts) USUALLY é£76.70. TEMPORARILY SAVE 1/3RD!!! This highly detailed conversion set enables the model maker to convert any Italeri C-130 in a stretched C-130J-30. Our set contains four fuselage plugs, a revised cargo ramp, a new cargo floor piece, new Antennae and ECM-equipment, finally correctly shaped...
Aircraft conversions (resin)
Special Offers
£51.15
-
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

Kora KORC72121
Dornier Do-17Z-2 Croatian service conversion set (designed to be used with Airfix kits)[Do 17Z-1] WAS é£16.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£11.33
-
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

Kora KORC7283
Polikarpov I-16 Typ 29 Soviet service II. Conversion set (designed to be used with Hasegawa kits) WAS é£19.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£13.16
-
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

Kora KORC7290
Mitsubishi MC-20-II Japan - Conversion Set (designed to be used with Planet Models) [Mitsubishi Ki-57 'Topsy'] WAS é£32.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£21.61
-
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

Profimodeller PF32161P
Aero L-39V tow version for Aero L-39 Albatros, conversion of L39C (designed to be used with HPH kits) WAS é£22.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£14.87
-
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

Kora KORCS7256
Arado Ar-68E-1 Ski (designed to be used with CMK and Huma Modell kits) WAS é£14.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£9.87
-
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

Armory ARAM72103
1:72
Mikoyan MiG-25R reconnaisance plane - conversion set (designed to be used with Condor Ukraine and Zvezda kits) WAS é£33.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£22.54
-
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

No Image
Print Scale PSR72002
de Havilland DH-82b Tiger Moth Queen Bee float plane conversion Part 1 The kit contains resin 3D and photo-etched parts, decal (designed to be used with Airfix kits) WAS é£33.30. NOW BEING CLEARED!!! SAVE MORE THAN 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£19.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
Kora KORC72137
Junkers Ju-88A-1 Flammwerfer special defense weapon (designed to be used with Revell kits) WAS é£29.50. TEMPORARILY SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£19.67
-
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

Kora KORC7254
Fokker C.X Early (Dutch service) (designed to be used with AZ Model kits) WAS é£22.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£14.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

Kora KORC7257
1:72
Fokker C.X HS Engine (Spanish Republican Air Force) (designed to be used with AZ Model kits) WAS é£22.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£14.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

Kora KORC7287
1:72
Polikarpov I-16 Typ 29 Romanian service Conversion set (designed to be used with ICM kits) WAS é£19.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£12.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

Kora KORCSD7266
1:72
Junkers Ju-87B-2/U-4 'Stuka' Ski - Conversion set & decal (designed to be used with Fujimi, Heller, Italeri and Revell kits) WAS é£20.60. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£13.74
-
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

Maestro Models MMMK4881
Saab J-29C Tunnan recce conversion. WAS é£26.99. NOW BEING CLEARED!! SAVE 1/3RD!!! The S29C Tunnan was a converted J29B Tunnan fighter, fitted with five cameras that replaced the guns - so no armament was carried on this version. It could be fitted with extra fuel tanks or ECM pods. The letter S is derived from the Swedish word Spaning, translating into reconnaissance. A total of 76 aircraft were built from 1954 through 1956....
Aircraft conversions (resin)
Special Offers
£17.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

Sun Details SD-R4413
Let L-13J motorised Blanik, conv. (designed to be used with MARK I Models kits) (incl. decals) WAS é£7.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£5.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

Quickboost (by Aires) QB48185
Grumman F6F-3N Hellcat conversion set (designed to be used with Eduard kits) WAS é£5.99. NOW BEING CLEARED!! HALF PRICE!!!
Aircraft conversions (resin)
Special Offers
£2.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

Kora KORC7264
Junkers Ju-86E-5 Romanian service - Conversion set (designed to be used with Italeri kits) WAS 27.99. NOW BEING CLEARED!! SAVE 1/3RD!! LAST ONES!!!
Aircraft conversions (resin)
Special Offers
£18.89
-
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

Kora KORC7289
Mitsubishi MC-20-I Manchukuo Conversion Set (designed to be used with Planet Model kits) [Mitsubishi Ki-57 'Topsy'] WAS é£32.40. BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£21.61
-
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

Kora KORC7293
Mitsubishi MC-20-II Chinese Air Force Conversion Set (designed to be used with Planet Model kits) [Mitsubishi Ki-57 'Topsy'] WAS é£32.40. BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£21.61
-
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

Kora KORCS7201
Fokker C.X Bristol Perseus in Finnish Service (designed to be used with AZ Model kits) WAS é£15.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£10.40
-
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

Bra.Z Models BZ4068
1:144
Airbus A300-600 Conversion NO DECALS INCLUDED (designed to be used with Revell kits)[Airbus A300-600ST 'Beluga'] WAS é£53.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£35.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

Kora KORC7255
1:72
Fokker C.X Trainer (Dutch service) (designed to be used with AZ Models kits) NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£14.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

Kora KORC7256
1:72
Fokker C.X HS Engine (Dutch Service) WAS é£22.50. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£14.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

Armory ARAM72107
1:72
Mikoyan MiG-25RB recon/bomber conversion set (designed to be used with Condor Ukraine and Zvezda kits) WAS é£34.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£23.33
-
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 (resin) AES48005
1:48
Lockheed-Martin F-16N Israeli Peace Marble II Avionics Spine for Hasegawa Lockheed-Martin F-16NJ (designed to be used with Hasegawa kits) WAS é£13.26. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£4.99
-
In Stock
5+

   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