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=304369 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 20 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

African Air Forces Mikoyan MiG-21 Part 3 : MiG-21bis 509 & 525 Congo-Brazzaville, MiG-21R ER-101 & MiG-21bis ER-102 Eritrea, MiG-21bis 75 Guinea-Bissau, MiG-21bis 223 Mozambique, MiG21bis 9799, 9811 & 9818 Uganda, Chengdu F-7NI 5 Sqn 701 Zimbabwe
Aircraft decals (military)
Catalogue
£6.50
-
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

Mitsubishi ZERO A6M3 Model 22-32: 2 Kokutai (Q-102) 1942, Kiyoshi (X-151) 1942, 582 Kokutai (188) 07/04/1943, 204 Kokutai (T2-190) 1943, 1 Kokutai (Z-162) 1942, Iwakumi Kokutai (TII-138) 1943, 261 Kokutai (159) 1943, Okumura (WI-108) 1943, Nishizawa (UI-106) 1943
Aircraft decals (military)
Catalogue
£8.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

Mitsubishi A6M2 Zero Model 21: Sakai (V-103 & V-107) 1941-42, Arita (V-141) 1942 , Iwamoto 1943, Nouno (Hiryu B11-140) 1941, Nikado (Kaga A11-121) 1941
Aircraft decals (military)
Catalogue
£8.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

African Air Forces Mikoyan MiG-17 Part 1: MiG-17F C24 & C25 Angola, MiG-17F BF 8401 Burkina Faso, MiG-17 118 Congo-Brazzaville, MiG-17F 1411 Ethiopia, MiG-17 513 Guinea-Conakry, MiG-17F 43 & 91 Guinea-Bissau
Aircraft decals (military)
Catalogue
£5.70
-
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

African Air Forces MiGs & Sukhois : MiG-23ML C436, C454 red and black & C-470 Angola; MiG-23BN ER-01 'Lilo', MiG-29 ERAF 508, MiG-29UB ERAF 501, Su-27 608 & Su-27UB 609 Eritrea; MiG-23BN 1288, MiG-23ML 1809 & Su-27 1951 Ethiopia; MiG-29SEh 614 & 623 Sudan; Su-30MK2 AF 011 & AF 015 Uganda ref.
Aircraft decals (military)
Catalogue
£8.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
de Havilland Vampire Mk.5 Aeronavale & Armeƒ© de l'air : 57.S-6 Port Lyautey 1960; 57.S-15, 57.S-7 & 57.S-14 Khourigba 1954-56; 5-AG & NL-A Dijon 1950-51; TU nƒ¢Ë ...¾10094 Meknes 1951; U nƒ¢Ë ...¾10080 Sidi-Ahmed 1956
Aircraft decals (military)
Catalogue
£8.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
Re-printed! Dassault-Mirage F1 CT 13-SA, 30-SY, 30-SG, 30-QR (4 schemes)
Aircraft decals (military)
Catalogue
£8.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

1:144
Dassault-Mirage IIIE Armeƒ© de l'Air Part 2: 2-ED Né°433 - EC 1/2 'Cigognes' - BA 102 Dijon 1975, 4-BB Né°556 - EC 2/4 'La Fayette' - BA 116 Luxeuil 1979.
Aircraft decals (military)
Catalogue
£8.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

Dassault-Mirage 2000-5 of the world: 2000-5EG nƒ¢Ë ...¾551 05/2010 & nƒ¢Ë ...¾554 11/2011 and 2000-5BG nƒ¢Ë ...¾505 & 506 in 09/2010, 331 Mira, Tanagra, Greece, Hellenic Air Force ; 2000-5EDA nƒ¢Ë ...¾QA91 and 2000-5DDA nƒ¢Ë ...¾QA87 Doha, Qatar, end of 2011, 2000-5EDA nƒ¢Ë ...¾QA95 and 2000-5DDA nƒ¢Ë ...¾QA85 03/2011, Souda, Crete, Operation "Unified Protector", Qatar Emiri Air Force; 2000-5Ei nƒ¢Ë ...¾2016 (EI16) & 2000-5Di nƒ¢Ë ...¾2057 (D...
Aircraft decals (military)
Catalogue
£9.70
-
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

Cessna L-19E in French service : 24-589 code AQI - AĮ'"n-Sefra Algeria 1958, 24-572 code AME - Biskra Algeria 1962, 23-311 code AQJ - Toussus-le-Noble 1973, 24-526 code BPN - Pau 1967, 24-566 code KBP - Djibouti 1981, 24-567 code CMN - Montpellier 1981, 24-572 code BVW - Berlin 1993, 24-578 code BCM - Dax 1978. [Cessna O-1 BIRD DOG]
Aircraft decals (military)
Catalogue
£8.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

1:144
Dassault-Mirage 2000C 5eme Escadre: 5-OZ, 5-NV, 5-OX, 5-AQ, 5-NI & 5-NT all based at Orange Air Base in the years 2000
Aircraft decals (military)
Catalogue
£8.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

1:144
Dassault-Mirage 2000C RDM : 330-AS, 2-EF, 2-LH; 2000C RDI : 12-YG, 12-YN, 12-KP; 2000-5F : 2-FK (7 schemes)
Aircraft decals (military)
Catalogue
£8.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

1:144
Dassault-Dornier Alpha Jet 8-MA (special scheme 60 years), 8-ML, 8-MH, 8-NN (4 schemes)
Aircraft decals (military)
Catalogue
£8.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

1:144
French Douglas SBD-5 Dauntless over Indochina: 3.F-4 Aircraft carrier Dixmude, Indochina 03/1947, 4.F-9 Aircraft carrier Dixmude, Indochina 1947/48 & 166 GAN 2 Cognac 1944/45
Aircraft decals (military)
Catalogue
£8.10
-
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:144
Dassault-Mirage F.1 M: Ala de Caza 14, 14-31 & 14-22 Nato Tiger Meet 09 Kleine Brogel (1+1 scheme)
Aircraft decals (military)
Catalogue
£8.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
Westland Wyvern S.4: N∞278 - 813 NAS H.M.S. Eagle 1958, N∞138 - 827 NAS H.M.S. Eagle 1955, N∞387 - 831 NAS Ark Royal 1957
Aircraft decals (military)
Catalogue
£8.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

1:144
French Curtiss SB2C-5 Helldiver over Indochina : 3.F-1 & 3.F-16 Bach Maƒ", Indochina april 1954, 9.F-9 & 9.F-7 Aircraft carrier Arromanches, Indochina november 1952 & 10.S.10 1953 (designed to be used with Arii kits)
Aircraft decals (military)
Catalogue
£8.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

1:144
Douglas C-47 Skytrain Indochina (3 schemes)
Aircraft decals (military)
Catalogue
£8.10
-
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
Republic P-47D Thunderbolt Bubbletop French 1944-45 : 'Navarre', 'Ardennes', 'Lafayette', 'Dauphine', 'Roussillon' (8+1 schemes)
Aircraft decals (military)
Catalogue
£8.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
Junkers 52/3m (AAC-1 'Toucan') French Navy & Air Force: 5.S.7 N∞32 - Karouba (Tunisia) 1960, DK.2 N∞216 - AOF Liaison Unit Dakar (Senegal), N∞127 code I - 1947, N∞50 code G - GT II/61 'Maine' Le Bourget 1948, N∞166 code O & N∞414 code EF- GT I/64 'Bearn' Tan Son Nhut (Indochina) 1948 & 1952, N∞? code J - GSRA 78 'Tindouf' Colomb-Bechar (Algeria) 1959 (7 schemes)
Aircraft decals (military)
Catalogue
£8.99
-
In Stock
2