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=362558 AND ps.id = 956 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 15 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

Tigerhead Decals THD72022
1:72
Hawker Sea Fury FB.11 Part 2 Iraq, Cuba, Canada
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72023
1:72
Turkish Huns North-American F-100C/F-100D/F-100F in TUAF Service
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72024
1:72
Supermarine Spitfire Mk.IXe in Turkish Service
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72025
1:72
Exotic Starfighters (Lockheed F-104 Starfighters in TUAF, Pak and Jordanian)
Aircraft decals (military)
Catalogue
£13.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

Tigerhead Decals THD72020
1:72
Supermarine Spitfires All Around the World. Supermarine Supermarine Spitfire Mk.Vc. It is possible to make 3 full profiles. - Danish Air Force 1946 - Turkish Air Force 1948 - Hellenic Air Force 1946
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72021
1:72
Hawker Hurricanes Mk.I Around the World. It is possible to make 3 full profiles. It is suitable for 1/72 Airfix kit. - Turkish Air Force - Finnish Air Force - Rumanian Air Force
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72018
1:72
Supermarine Spitfires in Soviet Service. Along with British Hurricanes, the Soviet Air Force (voyenno-vozdushnyye silyaβé-"VVS) also managed to fly another aircraft of the Royal Air Force as a front-line fighteraβé-"the Supermarine Spitfire Mk. Vb. In the West this airplane is feted as the winner of the Battle of Britain, and is also a national symbol of World War II. In the skies of Russia these fighters became participants in 19...
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72019
1:72
Supermarine Spitfire MK.IXe in International Service. - Turkish Air Force 1945 - Royal Norwegian Air force 1946 - Royal Egyptian Air Force 1948
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72008
Early Arabic Mikoyan MiG-21 F13 Fishbeds The Mikoyan MiG-21 (Russian: ƒ..."ƒéΈƒéºƒé¾ƒ'鏃é½ ƒéΈ ƒ"ƒ'†'ƒ'aš-ƒé΅ƒé²ƒéΈƒ'aβé-i ƒ..."ƒéΈƒ"-21; NATO reporting name: Fishbed) is a supersonic jet fighter aircraft, designed by the Mikoyan Design Bureau in the Soviet Union. It was popularly nicknamed 'Balalaika', from the aircraft's planform-view resemblance to the Soviet stringed musical instrument or oƒ...aβé-s'wek (English: penci...
Aircraft decals (military)
Catalogue
£13.40
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

Tigerhead Decals THD72016
'Italian Job' Macchi C.202/C.205 in International Service. The Macchi C.202 'Folgore' (Italian 'thunderbolt') was a World War II fighter aircraft built by Macchi Aeronautica and operated mainly by the Regia Aeronautica (RA; Royal (Italian) Air Force). Macchi aircraft designed by Mario Castoldi received the 'C' letter in their model designation, hence the 'Folgore' is referred to as the C.202 or MC.202. The C.202 was a developm...
Aircraft decals (military)
Catalogue
£13.40
-
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

Tigerhead Decals THD72017
1:72
The Lockheed T-33 Shooting Star (or T-Bird) is an American jet trainer aircraft. It was produced by Lockheed and made its first flight in 1948 piloted by Tony LeVier. The T-33 was developed from the Lockheed P-80/F-80 starting as TP-80C/TF-80C in development, then designated T-33A. It was used by the U.S. Navy initially as TO-2 then TV-2, and after 1962, T-33B. Despite its age, the T-33 remains in service worldwide. - Cuban...
Aircraft decals (military)
Catalogue
£11.70
-
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

Tigerhead Decals THD72013
1:72
Exotic Supermarine Spitfires. The Rolls-Royce Griffon engine was designed in answer to Royal Naval specifications for an engine capable of generating good power at low altitudes. The concepts for adapting the Supermarine Spitfire to take the new engine had begun as far back as October 1939; Joseph Smith felt that 'The good big 'un will eventually beat the good little 'un.' and Ernest Hives of Rolls-Royce thought that the Griff...
Aircraft decals (military)
Catalogue
£13.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

Tigerhead Decals THD72003
1:72
Convair F-102A Delta Dagger in Turkish, Greek and USAF markings
Aircraft decals (military)
Catalogue
£16.70
-
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

Tigerhead Decals THD72007
1:72
Turkish Spitfires. The Spitfires designed and produced by the British firm Vickers Supermarine participated the Battle of Britain and most probably they are the most popular fighters of WWII. Different models arrived Turkey at different times. A batch of 15 Supermarine Spitfire Mk.Is were ordered together with the Hawker 'Hurricanes' but only 3 of them were delivered. One of them which was an ex-Polish order arrived in Sept.19...
Aircraft decals (military)
Catalogue
£8.40
-
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

Tigerhead Decals THD72001
1:72
Allied Fighters under the Turkish Flag - Curtiss P-40B/P-40E Kittyhawk/Tomahawk
Aircraft decals (military)
Catalogue
£8.40
Out of Stock
0


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