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=362324 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 757 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

Master MR32-043
1:32
McDonnell F-4 Phantom II (short nose versions) - F-4B, F-4C, F-4D, F-4J, F-4N, F-4S and all British variants (designed to be used with Tamiya kits)
Aircraft detailing sets (brass)
Catalogue
£6.20
-
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

Master MR32-044
1:32
McDonnell F-4 Phantom II (long nose versions) - F-4E, F-4F, F-4G, F-4EJ and recce variants RF-4B, F-4C, F-4E, F-4EJ (designed to be used with Tamiya kits)
Aircraft detailing sets (brass)
Catalogue
£6.70
-
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

Master MR32-045
1:32
Dassault-Mirage III and Mirage 5 - Pitot Tube (designed to be used with Revell kits)
Aircraft detailing sets (brass)
Catalogue
£6.70
-
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

Master MR32-046
1:32
Mikoyan MiG-23MF/MiG-23ML Flogger - Pitot Tube (all variants except MLD version) (designed to be used with Trumpeter kits)
Aircraft detailing sets (brass)
Catalogue
£7.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

Master MR32-047
1:32
Mikoyan MiG-23MF/MiG-23ML MLD Flogger) - Pitot Tube (designed to be used with Trumpeter kits)
Aircraft detailing sets (brass)
Catalogue
£7.50
-
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

Master MR32-048
1:32
Mikoyan MiG-29A 'Fulcrum' - Pitot Tube (designed to be used with Trumpeter kits)
Aircraft detailing sets (brass)
Catalogue
£7.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

Master MR32-049
1:32
Mikoyan MiG-21SM/MiG-21M/MiG-21MF (Fishbed J) - Pitot Tube (designed to be used with Trumpeter kits)
Aircraft detailing sets (brass)
Catalogue
£9.65
-
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

Master MR48-048
1:48
Grumman F-14A Tomcat early version - nose tip & Angle Of Attack probe (designed to be used with Academy, Avant Garde , Eduard kits, Hasegawa and Italeri kits)
Aircraft detailing sets (brass)
Catalogue
£3.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

Master MR48-049
McDonnell F-4 Phantom II (short nose versions) - F-4B, F-4C, F-4D, F-4J, F-4N, F-4S and all British variants (designed to be used with Hasegawa kits)
Aircraft detailing sets (brass)
Catalogue
£4.80
-
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

Master MR48-050
1:48
McDonnell F-4 Phantom II (long nose versions) - F-4E, F-4F, F-4G, F-4EJ and recce variants RF-4B, F-4C, F-4E, F-4EJ (designed to be used with Hasegawa kits)
Aircraft detailing sets (brass)
Catalogue
£4.80
-
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

Master MR48-051
1:48
Heinkel He-162A-2 Salamander - armament and detail set (MG 151 barrel tips, nose gear indicator and Pitot Tube) (designed to be used with Dragon and Trimaster kits)
Aircraft guns (brass)
Catalogue
£4.80
-
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

Master MR48-052
1:48
Messerschmitt Me-163A Komet - armament set (MG 151 barrel tips) & Pitot Tube (designed to be used with Dragon, Italeri and Trimaster kits)
Aircraft guns (brass)
Catalogue
£4.80
-
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

Master MR48-053
1:48
Italian machine gun Breda SAFAT 7,7mm barrels (2pcs) As used on Fiat CR.42, Fiat G.50, Macchi C.200, Macchi C.202, Reggiane Re.2000 and most Italian aircraft of that era (designed to be used with Classic Airframe, Italeri, Hasegawa and Special Hobby kits)
Aircraft guns (brass)
Catalogue
£8.30
-
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

Master MR48-054
1:48
Italian machine gun Breda SAFAT 12,7mm barrels (2pcs). As used on Fiat CR.42, Fiat G.50, Macchi C.200, Macchi C.202, Reggiane Re.2000 and most Italian aircraft of that era (designed to be used with Classic Airframe, Italeri, Hasegawa and Special Hobby kits)
Aircraft guns (brass)
Catalogue
£8.30
-
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

Master MR48-055
German aircraft machine gun MG 15 - turned barrels and etched sights (2pcs). The World War II MG 15 was used in nearly all Luftwaffe aircraft with a flexible-mount defensive position. Heinkel He-111 Junkers Ju-88 , Junkers Ju-52/3m (designed to be used with Monogram and Revell kits)
Aircraft guns (brass)
Catalogue
£8.55
-
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

Master MR48-056
1:48
M197 - Three-barrelled rotary 20mm cannon - turned barrels with etched barrel clamps - used on Bell AH-1W Super Cobra and Bell AH-1S Attack Cobra (designed to be used with Fujimi, Italeri and Revell kits)
Aircraft guns (brass)
Catalogue
£9.80
-
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

Master MR48-057
1:48
Sepecat Jaguar GR.1/GR.3 Pitot Tube & Angle Of Attack probe (designed to be used with Airfix kits)
Aircraft detailing sets (brass)
Catalogue
£4.80
-
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

Master MR48-058
1:48
Mikoyan MiG-23M Flogger B - Pitot Tube (all variants except MLD version) (designed to be used with ESCI, Revell and Trumpeter kits)
Aircraft detailing sets (brass)
Catalogue
£4.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

Master MR48-059
1:48
Mikoyan MiG-23MLD Flogger - Pitot Tube
Aircraft detailing sets (brass)
Catalogue
£4.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

Master MR48-060
1:48
Mikoyan MiG-29 'Fulcrum' - Pitot Tube Mikoyan MiG-29AS Mikoyan MiG-29A Mikoyan MiG-29UB Mikoyan MiG-29A (designed to be used with Academy and Eduard kits)
Aircraft detailing sets (brass)
Catalogue
£4.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

Master MR48-061
1:48
Mikoyan MiG-21SM/MiG-21M/MiG-21MF Fishbed J - Pitot Tube (designed to be used with Academy, Eduard and Italeri kits)
Aircraft detailing sets (brass)
Catalogue
£7.85
-
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

Master MR72-032
Heinkel He-162 Salamander - armament and detail set (MG 151 barrel tips, nose gear indicator and Pitot Tube) (designed to be used with Dragon and Special Hobby kits)
Aircraft guns (brass)
Catalogue
£3.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

Master MR72-033
1:72
Messerschmitt Me-163B Komet - armament set (MG 151 barrel tips) & Pitot Tube (designed to be used with Condor and Special Hobby kits)
Aircraft guns (brass)
Catalogue
£3.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

Master MR72-034
1:72
Grumman F-14A Tomcat early version - nose tip & Angle Of Attack probe (designed to be used with Airfix, Hasegawa, Heller, Hobby Boss and Italeri kits)
Aircraft detailing sets (brass)
Catalogue
£2.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

Master MR72-035
1:72
McDonnell F-4 Phantom II (short nose versions) - F-4B, F-4C, F-4D, F-4J, F-4N, F-4S and all British variants (designed to be used with Airfix, Fujimi, Hasegawa, Italeri and Revell kits)
Aircraft detailing sets (brass)
Catalogue
£4.50
-
In Stock
5

[1]   «      19   |   20   |   21   |   22   |   23      »      [31]
-20 -10

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