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=362442 AND ps.id = 953
		 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 58 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

Profimodeller PF32022P
1:32
Mikoyan MiG-23MF/MiG-23ML intake detailing and wheel hubs/discs (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£5.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

Profimodeller PF32024P
1:32
Mikoyan MiG-23MF/MiG-23ML pylons, parachute fairing (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£20.30
-
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

Profimodeller PF32027P
1:32
Information/inscription plates
Aircraft detailing sets (etched)
Limited Availability
£4.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

Profimodeller PF32031P
1:32
Mikoyan MiG-23MF/MiG-23ML -nose wheel bay details (designed to be used with Trumpeter kits) [MiG-23MF Flogger B MiG-23ML Flogger G MiG-23MLD]
Aircraft detailing sets (etched)
Limited Availability
£11.30
-
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

Profimodeller PF32033P
1:32
Sukhoi Su-25K/Su-25UB details for bombs, rockets and fuel tanks (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£23.20
-
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

Profimodeller PF32035P
1:32
APU-68 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.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

Profimodeller PF32036P
1:32
FAB-100 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£2.25
-
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

Profimodeller PF32040P
1:32
ZB-500 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.30
-
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 PF32041P
1:32
UB-8MI detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Sukhoi Su-25 Frogfoot A, Sukhoi Su-25UB Frogfoot B, Sukhoi Su-27 Flanker B and Sukhoi Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.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

Profimodeller PF32045P
1:32
KH-28L detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£3.50
-
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

Profimodeller PF32046P
1:32
UPK-23 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.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

Profimodeller PF32047P
1:32
UPK-23 detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.60
-
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

Profimodeller PF32048P
1:32
S-25L detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.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

Profimodeller PF32049P
1:32
S-24B detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£3.50
-
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

Profimodeller PF32050P
1:32
MBD2-67U detail set. As used on Mikoyan MiG-21F-13, MiG-21MF Fishbed, MiG-21UM Mongol , MiG-23MF Flogger B, MiG-23ML Flogger G, MiG-23MLD Flogger K MiG-29K Fulcrum, MiG-29M Fulcrum, Sukhoi Su-25 Frogfoot A, Su-25UB Frogfoot B, Su-27 Flanker B and Su-27UB Flanker C (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched)
Limited Availability
£1.30
-
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

Profimodeller PF32063P
1:32
modern U.S. wheel chocks
Diorama accessories (resin)
Limited Availability
£6.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

Profimodeller PF32066P
1:32
Mikoyan MiG-15 Cockpit details (designed to be used with Trumpeter kits)
Aircraft detailing sets (resin)
Limited Availability
£11.30
-
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

Profimodeller PF32110P
1:32
Mikoyan MiG-23MF/MiG-23ML static electricity dipols [MiG-23 Mikoyan MiG-23MF Flogger B TU03210 MiG-23ML Flogger G TU03211 MiG-23MLD Flogger K (designed to be used with Trumpeter kits]
Aircraft detailing sets (brass)
Limited Availability
£5.60
-
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

Profimodeller PF32114P
1:32
Mikoyan MiG-21UM Oxygen tanks and Pitot tube (designed to be used with Trumpeter kits)
Aircraft detailing sets (resin)
Limited Availability
£5.30
-
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

Profimodeller PF32142P
1:32
Bell P-39 Airacobra external wing container barrels (designed to be used with Special Hobby kits) [Mk.I P-39F P-39Q P-39D P-39Q/N] - 4 brass parts
Aircraft detailing sets (brass)
Limited Availability
£6.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

Profimodeller PF32146P
1:32
Republic P-47D Thunderbolt pitot tube - photoetched part - brass part (designed to be used with Trumpeter kits)
Aircraft detailing sets (brass)
Limited Availability
£3.10
-
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

Profimodeller PF32149P
1:32
Heinkel He-162A-2 Salamander gun barrels (designed to be used with Best Choice and Revell kits) - 2 brass parts
Aircraft guns (brass)
Limited Availability
£3.50
-
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

Profimodeller PF32151P
1:32
correction set for FT-5 FIGHTER (Mikoyan MiG-17F) CHinese training double seater (MiG-17) new resin front lower fuselage part, brass barrel, new 2nd pitot tube on fuselage, rear fin with gummi bumper, side fuselage camera, warning racket (designed to be used with Trumpeter kits)
Aircraft detailing sets (etched and resin)
Limited Availability
£8.50
-
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 PF32170P
1:32
MG 15 barrels x 2
Aircraft guns (brass)
Limited Availability
£13.30
-
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

Profimodeller PF32196P
1:32
M-75 gun for North-American B-25 Gun Ship (designed to be used with Hong Kong Models)
Aircraft detailing sets (etched and resin)
Limited Availability
£41.99
-
In Stock
1

   1   |   2   |   3      »      [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