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=8278 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 28 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

No Image
Zotz ZTZ32085
Gerald "Jerry" Johnson: Ace of the PTO. This sheet will cover the aircraft from Pacific Theater Ace Gerald R. "Jerry" Johnson, featuring his Bell P-39D, Lockheed P-38F, P-38H, two different Republic P-47D's, Curtiss P-40N, and three different P-38L's. In all, you'll be able to depict 10 different aircraft using the sheet, covering the combat career of 22-kill ace Gerald R. Johnson from Adak, Alaska in 1942 through the end of t...
Aircraft decals (military)
Catalogue
£30.00
£27.00
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

No Image
Zotz ZTZ32087
Douglas A-20J in the ETO
Aircraft decals (military)
Catalogue
£29.99
£26.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

No Image
Zotz ZTZ32084
Douglas A-20G Havoc in the Pacific theatre of operations in WWII. Includes 'Bub's Brother' 'Jack's Hack' 'Joker' 'Jap Happy' 'Ravin Rachael' 'The Nervous Virgin' ' Sweet Lil Kitten' 'Baby Blitz II'
Aircraft decals (military)
Catalogue
£24.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

Zotz ZTZ32042
North-American P-51D Mustang (3) 44-7250566 82nd TRS Maj William A. Shomo 'The Flying Undertaker/Snooks 6th' Phillipines 1945; 44-14570 WD-D 335th FS/4th FG Capt Ted Lines 'Thunderbird' Red nose; 44-14111 PE-X 352nd FG Lt.Col.James D.Mayden 'Straw Boss 2/Little Sandra', Blue nose/red rudder. .
Aircraft decals (military)
Catalogue
£24.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

Zotz ZTZ32081
1:32
Boeing B-17F Flying Fortress At War part 1 Includes THE SHAMROCK / SNOOZIN'SUZAN / HONI KUU OKOLE / WAR BRIDE / FORT ALAMO / BLACK JACK
Aircraft decals (military)
Catalogue
£34.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

Zotz ZTZ32044
1:32
Republic F-84E/F-84G Thunderjet (2) USAF 49-2299 36th FWB Col Robert L.Scott CO Germany 1952; French 23023 11-TX EC 3/11 1955
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32082
Boeing B-17F Flying Fortress At War part 2 Includes Memphis Belle /Panama Hattie / Superman / Two Beauts / The Red Gremlin / Hell's Angels /
Aircraft decals (military)
Catalogue
£34.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

Zotz ZTZ32078
1:32
Grumman F-14A Tomcat pt.1
Aircraft decals (military)
Catalogue
£34.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

Zotz ZTZ32079
1:32
Grumman F-14A Tomcat pt.2
Aircraft decals (military)
Catalogue
£34.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

Zotz ZTZ32080
1:32
Vought RF-8A and RF-8G Crusader
Aircraft decals (military)
Catalogue
£34.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

Zotz ZTZ32053
1:32
Douglas A-4E Skyhawks in the Vietnam War (4) 149658 CF/24 or 151147 CF/20 VMA-211 Wake Island Avengers; 149996 AA/416 VA-46 Clansmen US Forrestal; 151073 NL/500 VA-155 Silver Foxes
Aircraft decals (military)
Catalogue
£24.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

Zotz ZTZ32041
1:32
Sukhoi Su-22M/3 Fitter (3) Russia Red 13 1st Air Squadron; Blue 23 274th Fighter Bomber Regt Afghanistan 1987; Peru with large Tiger Head on nose; Double sheet inc data.
Aircraft decals (military)
Catalogue
£24.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

Zotz ZTZ32069
1:32
Gloster Meteors F.8 & FR.9
Aircraft decals (military)
Catalogue
£24.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

Zotz ZTZ32063
1:32
McDonnell F-4E Phantom 388th TFW at Korat RTAB 1968 coded JJ and JV all with shark mouths Pt 1 (9) 70-309 Betty Lou; 70-275 Sweetie Pie; 70-371 Arizona Chicken; 70-279 Tiny Bubbles; 70-208 Here Come the Judge; 70-342 Lil Buddha; 70-290 Super Spook; 70-228 Nit Noy Boye II; 70-289 Gator. Includes brief history of each Phantom.
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32064
1:32
McDonnell F-4E Phantom 388th TFW at Korat RTAB 1968 coded JJ and JV all with shark mouths Pt 2 (10) 68-313 Spunky VI; 70-342 Easy Rockin Mama II; 70-279 Wreckin Crew; 70-268 TD's Teeloc; 70-309 El Toro Bravo; 80-332 Little Chris; 60-380 Diane; 67-339 Shamrock; 70-268 Paper Tiger; 70-306 War Lover. Includes a brief history of each Phantom.
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32062
1:32
North-American T-6 Texan Congo; T-6G 21 Sqn Force Arianne Congolaise, Central Gov. Force Leopoldville, Congo 1964. Flown in support of Mike Hoareis 5 Commando. Belgium; Harvard III, H-19 flown by Col. Louvignies from Kamina, Belgian Congo 1953-60. Up to 1962 flown from Ruanda-Urundi. Portugal; The FAP was the biggest user of Texans/Harvards for the liberation movements in her African colonies in the early 1960's. All s...
Aircraft decals (military)
Catalogue
£34.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

Zotz ZTZ32045
1:32
Republic F-84E/F-84G Thunderjet (3) USAF 10437/FS-437 58th FBW Col. Charles Jordan CO 'Midnight Take Off/Ruth'; Portugal 5128/128 Maj A.M.Rato destroyed in Luanda 1964; Greece 10373/FS373 340th Mira/ 11th Combat Wing 1954
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32061
1:32
Grumman A-6A Intruder (9) 151589 AG/512 and 151584 AF/507 VA-75 Sunday Punchers USS Independence 1965; 151785 NH/805, 151803 NH/810151787 NH/807 all VA-85 Black Falcons USS Kitty Hawk 1965/66; 155714 NE/504 VA-145 Swordsmen USS Ranger 11972/73; 155628 NH/51 VA-52 Knightriders USS Kitty Hawk 1972; 155707 ED/10 VMA(AW)-533 Nighthawks; All Lt Gull Grey/White camo; 151816/406 VA-65 Tigers USS Constellation 1966 experimental Olive ...
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32060
1:32
Lockheed F-104S/ASA Italian Starfighter's [F-104G]
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32058
Northrop P-61A/P-61B Black Widow
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32057
1:32
Douglas TA-4J Skyhawk 'Navy Scooters'
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32054
1:32
Douglas A-4M Skyhawk Last Of The Hot Rods
Aircraft decals (military)
Catalogue
£24.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

Zotz ZTZ32056
North-American B-25J Mitchells 8 gun straffers
Aircraft decals (military)
Catalogue
£29.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

Zotz ZTZ32038
1:32
North-American P-51D Mustangs (3) 44-13498/11 Maj H.Green CO 317th FS/325th FG Italy, black/yellow check tail, red nose; 44-14803 E9-V Lt Donald Vulgamore 376th FS/361st FG 'Jasper Joker II' yellow nose and rudder; No 42 Lt C.White 301st FS/332nd FG 'Creaners Dream' Italy , red nose and tail, Tuskegee Sqn.
Aircraft decals (military)
Catalogue
£24.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

Zotz ZTZ32001
1:32
General-Dynamics F-16A Fighting Falcon (1) 31 Sqn Belgian Air Force Tiger Meet 2002 Beja Potugal
Aircraft decals (military)
Catalogue
£16.99
-
In Stock
4

   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