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 ps.id = 957 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 37 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
Print Scale PSL047
1:144, 1:200, 1:24, 1:32, 1:35, 1:48
RAF Dark Earth strips
Aircraft decals (military)
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

No Image
Print Scale PSL048
1:144, 1:200, 1:24, 1:32, 1:35
RAF Dark Green strips
Aircraft decals (military)
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

No Image
Print Scale PSL049
1:144, 1:200, 1:24, 1:32, 1:35, 1:48
RAF Medium Sea Gray strips
Aircraft decals (military)
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

No Image
Print Scale PSL14423
Avro Vulcan B.2 Part 1 1. Vulcan B1 XA 901 of No. 101 Sqn, RAF Waddington 1962. 2. Vulcan B1A XA 910 of the Waddington Wing, RAF Waddington, September 1964. 3. Avro Vulcan B.2 XL 427 of No. 83 Sqn, RAF Scampton 1963. 4. Avro Vulcan B.2 XM 604 of No. 35 Sqn, RAF Coningsby 1964. 5. Vulcan B 2BS XL 317 of No. 617 Sqn, RAF Scampton 1963. 6. Vulcan B 2 XH 556 of No. 230 OCU, RAF Finningley 1963.
Aircraft decals (military)
Catalogue
£11.80
-
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

No Image
Print Scale PSL14424
1:144
Avro Vulcan B.2 Part 2 1. Avro Vulcan B.2 XL 444 of No. 617 Sqn, RAF Scampton, mid-1970s. 2. Vulcan K2 XL 445 of No. 50 Sqn, RAF Waddington, 1983. 3. Avro Vulcan B.2 XM 602 of No. 1 Group, Giant Voice 1971, McCoy AFB, Florida, 1971. 4. Avro Vulcan B.2BS XM 610 of No. 44 Rhodesia Sqn, RAF Scampton, January 1969. 5. Avro Vulcan B.2 XL 391 of No 9 Sqn, RAF Waddington, 1976. 6. Avro Vulcan B.2 XJ 783 of No 35 Sqn...
Aircraft decals (military)
Catalogue
£11.80
-
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

Print Scale PSL14422
North-American F-51 Mustang. Units of the Korean War 1. F-51D-25 45-11410 of the 45th TRS/67th TRW, Taegu AB (K-2), summer 1951. 2. F-51D-30 45-11613 of the 45th TRS/67th TRW, Kimpo AB (K-14), September 1951. 3. F-51D-30 45-11412 of the 12th FBS/18th FBG, Chinhae AB (K-10), March 1951. 4. F-51D-30 44-74283 of the 80th FIS/8th FBG, Seoul City AB (K-16), December 1950. 5. F-51D of No 1 Sqn, RoKAF, Pohang AB (K-3),...
Aircraft decals (military)
Catalogue
£14.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

Print Scale PSL14420
1:144
Messerschmitt Bf-109F-4 1. Me 109 F-4 tropic 5/JG27 Libya in 1942. Pilot: sergeant Hans Niederhoferr. 2. Me 109 F-4Z Tropic JG 27 Martooba (Libya), november 1942. Pilot: hauptmann (Captain) Werner Schroer. 3. Me 109F-4 'Yellow 4' flown by Oberfeldwebel Eberhard von Boremski, 9./JG 3 'Udet', May 1942. 4. Me 109F-4 8/JG Herzas (Ace of hearts). Russian front in 1941 was. 5. Me 109F-4B Stab / JG 52.Tiraspol October 19...
Aircraft decals (military)
Catalogue
£11.80
-
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

Print Scale PSL14421
1:144
Polikarpov I-16 / 1. I-16 tip 10, Major General Ivan Lakeyev. June 1941. 2. I-16 type 24. Defense of Leningrad, 1941. 3. I-16 type 29. 156 IAP winter 1941-1942. 4. I-16 Type 10 (or Type 18),1940-41. This plane was used army ruler Shchen Shih Tsai against Uighur Muslim separatist rebels. 5. And 16 type 10. Unidentified Air Force unit of the Kuomintang. Presumably the end of 1939. 6. I-16 type 24. One of the four...
Aircraft decals (military)
Catalogue
£11.80
-
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

Print Scale PSL041
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 65v.1 stripes
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

Print Scale PSL042
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 70 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL043
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 71 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL044
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 74v.1 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL045
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 79 stripes
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

Print Scale PSL046
1:24, 1:32, 1:35, 1:48, 1:72
Luftwaffe RLM 75 stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL14410
1:144
McDonnell F-4C/F-4D/F-4E Phantom II in Vietnam war [F-4C/D]
Aircraft decals (military)
Catalogue
£11.80
-
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

Print Scale PSL14419
1:144
McDonnell-Douglas F/A-18C Hornet 1. F/A-18C Bu No.163777 NG 300 2. F/A-18C Bu No.164909 NF 300 3. F/A-18C Bu No. 163730 WD 01 4. F/A-18C Bu No. 164971 NF 305
Aircraft decals (military)
Catalogue
£11.80
-
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

Print Scale PSL034
1:24, 1:32, 1:35, 1:48, 1:72
White stripes
Aircraft decals (military)
Catalogue
£7.99
-
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

Print Scale PSL035
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Yellow stripes
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

Print Scale PSL036
1:24, 1:32, 1:35, 1:48, 1:72
Red stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL037
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Black stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL038
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Green stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL039
1:24, 1:32, 1:35, 1:48, 1:72
Aluminium stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL040
1:144, 1:200, 1:24, 1:32, 1:35, 1:48, 1:72
Blue stripes
Aircraft decals (military)
Catalogue
£7.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

Print Scale PSL14416
1:144
Hawker Hurricane Mk.I/Mk.IIB Battle Of Britain Aces
Aircraft decals (military)
Catalogue
£11.80
-
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

Print Scale PSL14418
1:144
Supermarine Spitfire Mk.Vb/Mk.Vc; Seafire Mk.IIC, Mk.III,
Aircraft decals (military)
Catalogue
£11.80
-
In Stock
3

   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