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=339360 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 1,162 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

1:72
Boeing B-17G Fortress 42-32024 WA-L 'Swamp Fire' 379th BG, 524th BS - B-17F 42-5077 OR-T 'Delta Rebel II' 323rd BS, 91st BG April 1944
Aircraft decals (military)
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

1:48
Boeing B-29A-25-MO Super Fortress 42-65306 'The Outlaw' 19BG 28BS Okinawa 1951
Aircraft decals (military)
Catalogue
£12.95
-
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

1:32
Republic P-47N-2-RE Thunderbolt 119 'Chatauqua'463FS 507FG - P-47N 44-88835 'Red E Ruth' 19FS 318FG Le Shima 1945 Pilot Capt Don Stuck
Aircraft decals (military)
Catalogue
£10.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

1:32
Lockheed P-38J-15-LO Lightning 43-28677 H5-B 'Little Buckaroo' 392FS 367FG - P-38L H5-C 'Gung Ho' 392FS 367FG
Aircraft decals (military)
Catalogue
£10.20
-
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

1:144
Consolidated B-24H Liberator Phil Brinkman's Zodiacs Leo, Leo (version 2), Gemini, Capricorn, 834th BS 486thBG 1/144 Scale Set 1
Aircraft decals (military)
Catalogue
£7.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

1:144
Consolidated B-24H Liberator Phil Brinkman's Zodiacs Virgo, Sagittarius, Aquaria, Aries, 834th BS 486th BG 1/144 Scale Set 2
Aircraft decals (military)
Catalogue
£7.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

1:144
Boeing B-29-25-MO 'Super Fortress' 42-65306 'The Outlaw' 19BG 28BS Okinawa 1951 - B-29-85-BW 44-87660 'Celestial Queen'é 40BG 44BS 58BW 1945 - B-29-40-BW 42-24614 'Joltin' Josie, The Pacific Pioneer' T-SQ-5 498BG 873BS 20AF Jan 1945 - B-29-40-MO 44-27326 'United Notions' 92BG 325BS August 1950
Aircraft decals (military)
Catalogue
£7.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

1:48
Boeing B-29A-40-BW Superfortress 42-24614 'Joltin' Josie, The Pacific Pioneer' T-SQ-5 498BG 873BS 20AF Jan 1945 - B-29A-40-MO 44-27326 'United Notions'é 92BG 325BS August 1950
Aircraft decals (military)
Catalogue
£12.95
-
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

1:48
Boeing B-17F-20-DL Flying Fortress 42-3073 'Lightning Strikes' 410BS 91BG - B-17G-45-BO 42-97400 (44-83563) 'Fuddy Duddy' 708BS 447BG
Aircraft decals (military)
Catalogue
£10.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

1:48
Republic P-47N-2-RE Thunderbolt 119 'Chatauqua'463FS 507FG - P-47N 44-88835 'Red E Ruth' 19FS 318FG Le Shima 1945 Pilot Capt Don Stuck
Aircraft decals (military)
Catalogue
£10.20
-
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

1:48
Lockheed P-38J-15-LO Lightning 43-28677 H5-B 'Little Buckaroo' 392FS 367FG - P-38L H5-C 'Gung Ho' 392FS 367FG
Aircraft decals (military)
Catalogue
£10.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

1:72
Boeing B-29-25-MO Super Fortress 42-65306 'The Outlaw'é 19BG 28BS Okinawa 1951 - B-29-85-BW 44-87660 'Celestial Queen' 40BG 44BS 58BW 1945
Aircraft decals (military)
Catalogue
£10.20
-
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

Boeing B-29-40-BW Super Fortress 42-24614 'Joltin' Josie, The Pacific Pioneer' T-SQ-5 498BG 873BS 20AF Jan 1945 - B-29-40-MO 44-27326 'United Notions' 92BG 325BS August 1950
Aircraft decals (military)
Catalogue
£10.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

Martin B-26B Marauder 42-95828 Pinks Lady' 584 BS/584 BG - B-26B Marauder 42-96072 'Dina Might'' 573 BS/391 BG
Aircraft decals (military)
Catalogue
£10.20
-
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

1:72
Boeing B-17F-20-DL Flying Fortress 42-3073 'Lightning Strikes' 410BS 91BG - B-17G-45-BO 42-97400 (44-83563) 'Fuddy Duddy' 708BS 447BG
Aircraft decals (military)
Catalogue
£10.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

1:72
Boeing B-17F-115-BO Flying Fortress 42-30721 'Sweet and Lovely' 553BS, 381BG - B17G-30-BO 42-31909 'Nine O Nine' 323BS 91BG (Collings Foundation)
Aircraft decals (military)
Catalogue
£10.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

1:72
Lockheed P-38J-15-LO Lightning 43-28677 H5-B 'Little Buckaroo' 392FS 367FG - P-38L H5-C 'Gung Ho' 392FS 367FG
Aircraft decals (military)
Catalogue
£10.20
-
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

1:72
Republic P-47N-2-RE 119 Thunderbolt 'Chatauqua'463FS 507FG - P-47N 44-88835 'Red E Ruth' 19FS 318FG Le Shima 1945 Pilot Capt Don Stuck
Aircraft decals (military)
Catalogue
£10.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

1:48
Supermarine Spitfire Mk.XIX. Includes Pre and Post War Roundels, Generic Serials and Detailed Stencilling includes: Propeller Lock Stencils, Trestle Markers, Walkway Inboard, Wheels Up When Indicator Flush, Walkway Forward, Jack Here, Do Not Lift Under Here, 24 Volts Ground Supply, Hood Release Other Side, To Slide Hood Back Press Button, Oil Capacity 5 Gallons Air Space 2 Gallons, Fuel 100 -130 Galls, DTD, Black Walkway Lines...
Aircraft decals (military)
Catalogue
£10.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

1:48
Supermarine Seafire Mk.XVII, Mk.46, Mk.47. Includes markings for Squadrons: 736 - 759 - 766 -800 - 804 - 1832 '1833.
Aircraft decals (military)
Catalogue
£10.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

1:32
North-American B-25J Mitchell General Stencilling -Stars & Bars suitable for Medium Bombers - B-17 Flying Fortress & B-26 Marauders P-61, suited for the HK Model kits. Two types of propeller decals, two types of propeller warning strips plus blank set, black 2' walkway lines, Two of the more commonly used star-&-bar markings, two styles of fuel filler decals, two styles of escape hatch markings (starboard side only?) and th...
Aircraft decals (military)
Catalogue
£10.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

1:32
Republic P-47D Thunderbolt (2) 42-27210 or 42-21055 G9-E Look NO Hands Col van Etten 405th FS /509th FG; 44-32747 2Z-E Eight Nifties Maj Clyde Knisley 510th FS /405th FG
Aircraft decals (military)
Catalogue
£10.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

1:32
Supermarine Spitfire Mk.VIII (2) A58-517 UP-F Hava Go Jo Flt/Lt N Smithells; A58-526 UP-L Avagrog, both 79 sqn RAAF Morotai 1945.
Aircraft decals (military)
Catalogue
£10.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

1:32
Curtiss P-40F Warhawk/Kittyhawk Painted by Cpl Joseph E. Pumphrey (censured tails) P-40F X2-1 Fifinella 86th FS /79th FG; X4-3 Miss Memphis 85th FS 79th FG MTO 1943.
Aircraft decals (military)
Catalogue
£10.20
-
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

1:144
Boeing B-29A Superfortress (4) 44-87657 Command Decision 19BG/28BS; 45-21768 Kee Bird 46th Rec Squadron,red tail; 44-69763 Top Of The Mark 93BS/19BG two versions; 44-6206 Spirit of Freeport Circle E 22nd BW;
Aircraft decals (military)
Catalogue
£7.99
-
In Stock
3