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=362639
		 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 13 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
DK Decals DKD32011
de Havilland B-36 Mosquito in CzAF WAS é£13.20. TEMPORARILY SAVE 1/3RD!!! 1. B-36 (Mosquito FB Mk.VI), PZ247, pilot Alois ...é tanc, 2nd Flt., Air Regiment No.24, 1947 2. B-36 (Mosquito FB Mk.VI), TE603, pilot Vlastimil VeselÆ'é½, Air Regiment No.24, Ruzyn? Air Day, 7th September 1947 3. B-36 (Mosquito FB Mk.VI), MM430, Air Regiment No.25, 1950
Aircraft decals (military)
Special Offers
£8.80
-
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
DK Decals DKD32013
Supermarine Spitfire Mk.VB of No.313 (Czechoslovak) Squadron during "Operation Starkey" WAS é£8.80. TEMPORARILY SAVE 1/3RD!!! 1. Spitfire Mk.VB, EP110, F/L O. Ku"éera, Hawkinge, September 1943 2. Spitfire Mk.VB, AA969, F/L A. Hochmal, Hawkinge, September 1943 3. Spitfire Mk.VB, BL767, P/O PÆ'épa, Hawkinge, September 1943
Aircraft decals (military)
Special Offers
£5.87
-
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
DK Decals DKD32014
Supermarine Spitfire Mk.VB of No.313 (Czechoslovak) Sq., Sgt PavLk's cartoons, WAS é£16.70. TEMPORARILY SAVE 1/3RD!!! Pt.1 1. Spitfire Mk.VB, AB276, F/Lt V. Hajek 2. Spitfire Mk.VB, AD380, F/O A. Hochmal 3. Spitfire Mk.VB, BL973, F/L S. Fejfar 4. Spitfire Mk.VB, BM242, S/L K. Mrazek 5. Spitfire Mk.VB, BM301, F/O J. P...a"Æ'éhoda 6. Spitfire Mk.VB, W3962, F/O K. Vykoukal
Aircraft decals (military)
Special Offers
£11.14
-
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
DK Decals DKD32015
Supermarine Spitfire Mk.VB of No.313 (Czechoslovak) Sq., Sgt PavLk's cartoons, Pt.2 WAS é£16.70. TEMPORARILY SAVE 1/3RD!!! 1. Spitfire Mk.VB, BL581, F/L K. Vykoukal 2. Spitfire Mk.VB, AA865, Sgt. V. Truhla...a" 3. Spitfire Mk.VB, P8537, Sgt. J. Hlou...é¾ek 4. Spitfire Mk.VB, P/O J. Ku"éera 5. Spitfire Mk.VB, AD384, Sgt. O. Kresta 6. Spitfire Mk.VB, BM306, F/O V. JÆ'écha
Aircraft decals (military)
Special Offers
£11.14
-
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
DK Decals DKD32017
de Havilland Mosquito PR Mk.IX/Mk.XVI of Czechoslovak airmen WAS é£8.80. TEMPORARILY SAVE 1/3RD!!! 1. Mosquito PR Mk.IX, LR422, S/Ldr E.B. Kraus, No 540 Sq. RAF, October 1943 2. Mosquito PR Mk.IX, LR432, W/O F. Kepra + F/Lt K. Vokoun, No.544 Sq. RAF, November 1944 3. Mosquito PR Mk.XVI, NS502M, F/Lt J. Ku"éera + F/O O. Filip, No.544 Sq RAF, November 1944
Aircraft decals (military)
Special Offers
£5.87
-
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
DK Decals DKD48034
No.313 (Czechoslovak) Sq. Supermarine Spitfires, Pt.1 WAS é£17.40. TEMPORARILY SAVE 1/3RD!!! 1. Spitfire Mk.I, X4031, F/L K. Mrazek 2. Spitfire Mk.VB, W3962, F/O K. Vykoukal 3. Spitfire Mk.VB, AA865, Sgt. V. Truhla...a" 4. Spitfire Mk.VB, BL973, F/L S. Fejfar 5. Spitfire Mk.VB, AD380, F/O A. Hochmal 6. Spitfire Mk.VB, P8537, Sgt. J. Hlou...é¾ek 7. Spitfire Mk.VB, BM301, F/O J. P...a"Æ'éhoda 8. Spitfire Mk.VB, AA969, F...
Aircraft decals (military)
Special Offers
£11.60
-
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
DK Decals DKD48035
No.313 (Czechoslovak) Sq. Supermarine Spitfires, Pt.2 WAS é£17.40. TEMPORARILY SAVE 1/3RD!!! 1. Spitfire Mk.IIA, P7834, F/O F. Fajtl 2. Spitfire Mk.VB, BL581, F/L K. Vykoukal 3. Spitfire Mk.VB, AB276, F/Lt V. Hajek 4. Spitfire Mk.VB, s/n unknown, P/O J. Ku"éera 5. Spitfire Mk.VB, AA757, F/L S. Fejfar 6. Spitfire Mk.VB, BM242, S/L K. Mrazek 7. Spitfire Mk.VB, BM306, F/O V. JÆ'écha 8. Spitfire Mk.VB, EP110, F/L O. K...
Aircraft decals (military)
Special Offers
£11.60
-
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
DK Decals DKD48036
Slovak National Uprising 1944 - Allied Air Support WAS é£14.99. TEMPORARILY SAVE 1/3RD!!! 1. North-American P-51B-5-NA Mustang, 43-6583, 2nd Lt. E.A. Smith, 4th FS, 52nd FG, Tri Duby airfield, September 17, 1944 2. P-51C-10-NT Mustang, 43-25101, 2nd Lt. A.F. Watkins, 5th FS, 52nd FG, Tri Duby airfield, September 17, 1944 3. Boeing B-17G-50-DL Flying Fortress, 44-6325, 816th BS, 483rd BG, Maj. F.J. Ascani, Tri Duby airfield,...
Aircraft decals (military)
Special Offers
£9.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
DK Decals DKD48037
Boeing B-17F/B-17G Flying Fortress 15th Air Force WAS é£14.99. TEMPORARILY SAVE 1/3RD!!! 1. B-17F-55-BO, 42-29485 'MISS FURY' 416th BS/99th BG, early 1944 2. B-17G-20-BO, 42-31473 'MY BABY' 20th BS/2nd BG, summer 1944 3. B-17G-25-VE, 42-97719 'GODDESS ON A FORTRESS' 774th BS/463rd BG, summer 1944
Aircraft decals (military)
Special Offers
£9.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
DK Decals DKD48058
No.310 Sq. RAF - Hawker Hurricanes of Czechoslovak pilots USUALLY é£20.50. TEMPORARILY SAVE 1/3RD!!! 1. Hurricane Mk.I, P3143, F/L G.L. Sinclair/Sgt. B. F¼rst, September 1940 2. Hurricane Mk.I, R4089, F/L J. Jefferies, September 1940 3. Hurricane Mk.I, V6556, Sgt. E. Prchal, November 1940 4. Hurricane Mk.I, V6579, S/L G.D.M. Blackwood, winter 1940/1941 5. Hurricane Mk.I, P3889, P/O E. Fechtner, October 1940 6. H...
Aircraft decals (military)
Special Offers
£13.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
DK Decals DKD48P01
Brian A. Eaton and his Mustangs WAS é£15.80. TEMPORARILY SAVE 1/3RD!!! 1. North-American Mustang Mk.III, FB260, No.239 Wing, G/C B. Eaton, Italy 1944 2. Mustang Mk.IVA, KH745, No.239 Wing, G/C B. Eaton, Italy 1945 3. P-51D-25 Mustang, A68-803, No.81 Wing, O/C B. Eaton, Japan 1947
Aircraft decals (military)
Special Offers
£10.54
-
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
DK Decals DKD72086
1:72
North-American F-6D/K Mustang over the Pacific [P-51D] WAS é£17.40. TEMPORARILY SAVE 1/3RD!!! 1. F-6D, 44-14873, Lt. P. Lipscomb, 82nd TRS, 71st TRG, the Philippines 1945 2. F-6D, 44-14841, Capt. W. Shomo, 82nd TRS, 71st TRG, the Philippines 1945 3. F-6D, 44-14841, Maj. W. Shomo, 82nd TRS, 71st TRG, the Philippines 1945 4. F-6D, 44-14841, Maj. W. Shomo, 82nd TRS, 71st TRG, the Philippines 1945 5. F-6K, 44-11920, Maj....
Aircraft decals (military)
Special Offers
£11.60
-
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
DK Decals DKD72091
1:72
No.313 (Czechoslovak) Squadron Supermarine Spitfires WAS é£17.40. TEMPORARILY SAVE 1/3RD!!! 1/ Spitfire Mk.I, X4031, F/L K. Mrazek 2/ Spitfire Mk.IIA, P7834, F/O F. Fajtl 3/ Spitfire Mk.VB, W3962, F/O K. Vykoukal 4/ Spitfire Mk.VB, AB276, F/Lt V. Hajek 5/ Spitfire Mk.VB, AA865, Sgt. V. Truhla...a" 6/ Spitfire Mk.VB, AA757, F/L S. Fejfar 7/ Spitfire Mk.VB, AD380, F/O A. Hochmal 8/ Spitfire Mk.VB, P8537, Sgt. J. Hlou...é¾...
Aircraft decals (military)
Special Offers
£11.60
-
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