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=362108
		 AND P.product_category_id = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 18 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 113
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

Print Scale PSL007
Panzerwaffe camouflage Ardennes, December 1944 LAST ONES NOW BEING CLEARED!!! WAS é£9.50. SAVE 1/3RD!!!
Military vehicle decals
Special Offers
£6.34
-
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

No Image
Print Scale PSL48183
1:48
de Havilland DH-82b Tiger Moth Queen Bee Part-3 WAS é£17.30. TEMPORARILY HALF PRICE!!! 1. de Havilland D.H.82B Queen Bee, L-5894/301, St Athan, 1940-41. Dark Earth/Dark Green upper surfaces with Silver undersides and floats. Codes and serials in black, the latter repeated below both wings. Blood Red tips of upper wings. Blue/Red national markings above top wings. 2. de Havilland D.H. 82B Queen Bee, N-1837, of 'W' Flight,...
Aircraft decals (military)
Special Offers
£8.65
-
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

No Image
Print Scale PSL48185
Bell UH-1 Air Ambulance in Vietnam War Part 1 WAS é£17.99. TEMPORARILY HALF PRICE!!! 1. UH-1H,(Iron Butterfly) 66-16432, 45th Medical Company, Bien Hoa, 1969. 2. UH-1H,(Ohio State 56-13) 68-16489, 498th Medical Company, LZ English September, 1970. 3. UH-1H,(Day Tripper) 66-16542, 82th Medical Company, IV Corps, 1970. 4. UH-1H,(Green Weenie) 66-17005, 283th Medical Company, Dak To, 1968.
Aircraft decals (military)
Special Offers
£8.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

No Image
Print Scale PSL48186
1:48
Bell AH-1 Cobra Part 1 WAS é£17.99. TEMPORARILY HALF PRICE!!! 1. Bell AH-1W Super Cobra (209) Reg.: 165369. MSN: 26369. Code: SM-23 Location & Date.El Centro - NAF (NJK / KNJK) California, USA - February 19, 2015. 2. AH-1W Lot.I Super Cobra Unit: HMLA-169 Vipers, USMC. Serial: 140 (BuNo.162535). 3. AH-1W. Unit: USMC. Serial: 237. During Operation Desert Storm, this USMC AH-1W was armed with 70mm rockets and TOW missil...
Aircraft decals (military)
Special Offers
£8.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

No Image
Print Scale PSL48189
Bell UH-1 Air Ambulance in Vietnam War Part 2 WAS é£17.99. TEMPORARILY SAVE 1/3RD!!! 1. UH-1H,(Flower Power) 66-16429, 45th Medical Company, Lai Khe, 1968. 2. UH-1H,(Bandaid Bandit) 70-15769, 571th Medical Company, Phu Bai, 1972. 3. UH-1H,(Woodstock) 68-15230, 57th Medical Company, Phu Bai, 1971. 4. UH-1H,(Iron Butterfly) ,69-15238, 498th Medical Company, Pleiku, 1970.
Aircraft decals (military)
Special Offers
£11.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

No Image
Print Scale PSL48208
Bristol Beaufighter TF Mk X Part 3 WAS é£17.30. TEMPORARILY SAVE 1/3RD!!! 1 Beaufighter TF.Mk.X. Unit: 254 Sqn, Coastal Command RAF. Serial: QM-F (Rd351) RAF Coastal Command, mid-1944.
Aircraft decals (military)
Special Offers
£11.53
-
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

No Image
Print Scale PSL48209
Douglas SBD Dauntless & Banshee in Combat. Part 4 WAS é£17.30. TEMPORARILY SAVE 1/3RD!!! 1 A-24B-1-DO (SBD-5) 42-54298 "Bar Fly" 407th Bomb Group (Dive) Amchitka, Territory of Alaska July-August 1943. 2 SBD-5, "White 51", NZ 5051, flown by F/Lt. J. W. Edwards & F/Sgt. L. A. Hoppe, No 25 Dove Bombing Squadron RNZAF, Piva, Bougainville, April-May 1944.
Aircraft decals (military)
Special Offers
£11.53
-
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

No Image
Print Scale PSL48211
Bristol Beaufighter Mk.X Part 1 (with 3D decal Instrument panel) WAS é£24.99. TEMPORARILY HALF PRICE!!! 1. Beaufighter TF.Mk.X. Unit: 45 Sqn, RAF. Serial: OB-O (RD 815). Kuala Lumpur, Malaya, November 1949.
Aircraft decals (military)
Special Offers
£12.49
-
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

No Image
Print Scale PSL48212
Bristol Beaufighter Mk.X Part 2 WAS é£24.99. TEMPORARILY HALF PRICE!!! (with 3D decal Instrument panel) 1. Beaufighter TF.Mk.X. Unit: 45 Sqn, RAF. Serial: OB-A (RB858).
Aircraft decals (military)
Special Offers
£12.49
-
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

No Image
Print Scale PSL48215
1:48
Bell/Boeing MV-22B Osprey Part 2 WAS é£17.99. TEMPORARILY SAVE 1/3RD!!! 1. MV-22 Osprey 168238/ EG 00 aircraft with Marine Medium Tiltrotor Squadron (VMM) 263 (Reinforced), 22nd Marine Expeditionary Unit.
Aircraft decals (military)
Special Offers
£11.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

No Image
Print Scale PSL72414
Lockheed F-104 Starfighter technical stencils DOUBLE SHEET [F-104C F-104J] WAS é£21.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£14.66
-
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

No Image
Print Scale PSL72447
Lockheed F-80 USA & Europe Part 1 WAS é£19.99. TEMPORARILY SAVE 1/3RD!!! 1. Lockheed F-80B-1-LO Shooting Star 45-8598/ FN-598, flown by Col. Harry Russell Spicer, commander of the 36 th Fighter Group, Fƒ¼rstenfeldbruck Air Base, Germany, 1948. 2. Lockheed F-80A-5-LO Shooting Star 44-85464/FN-464 flown by Col. David Schilling, commander of the 56th Fighter Group, " Operation Fox Able l", Fƒ¼rstenfeldbruck Air Base, Germany,...
Aircraft decals (military)
Special Offers
£13.33
-
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

No Image
Print Scale PSL72448
Lockheed F-80. USA & Europe Part 2 WAS é£15.99. TEMPORARILY SAVE 1/3RD!!! 1. Lockheed F-80C-10 Shooting Star 49-653/ FT-653, flown by Col. William T. Samways, commanding officer of the 8th Fighter Bomber Group, Itazuke AB, Japan, June 1950. 2. Lockheed F-80C-10 Shooting Star 49-665/FT-665, flown by Col Irwin H Dregne, commanding officer of the 51st Fighter Intercept Group, Tsuiki AB, Japan, July 1951. 3. Lockheed F-80C...
Aircraft decals (military)
Special Offers
£10.67
-
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

No Image
Print Scale PSR48024
Daimler-Benz DB 601 WAS é£35.99. NOW BEING CLEARED!! SASVE 1/3RD!!!
Aircraft engines and propellers (resin)
Special Offers
£23.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

No Image
Print Scale PSR72002
de Havilland DH-82b Tiger Moth Queen Bee float plane conversion Part 1 The kit contains resin 3D and photo-etched parts, decal (designed to be used with Airfix kits) WAS é£33.30. NOW BEING CLEARED!!! SAVE MORE THAN 1/3RD!!!
Aircraft conversions (resin)
Special Offers
£19.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

No Image
Print Scale PSR72005
1:72
Bomb trolley with the lift. Luftwaffe WWII. SC 250 JA, L, L2 The kit contains resin 3D and photo-etched parts, decal WAS é£26.99. NOW BEING CLEARED!!! SAVE 1/3RD!!!
Diorama accessories (resin)
Special Offers
£17.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

No Image
Print Scale PSR72006
Bomb trolley with the lift. Luftwaffe WWII. SC 250 JB, JC, K.The kit contains resin 3D and photo-etched parts, decal WAS é£26.99. NOW BEING CLEARED!!! SAVE 1/3RD!!!
Diorama accessories (resin)
Special Offers
£17.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

No Image
Print Scale PSR72007
1:72
Bomb trolley with the lift. Luftwaffe WWII. SC 250 Stabo.The kit contains resin 3D and photo-etched parts, decal WAS é£26.99. NOW BEING CLEARED!!! SAVE 1/3RD!!!
Diorama accessories (resin)
Special Offers
£17.99
-
In Stock
5


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