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=18581 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 17 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
1:72
Beaching trolley for Kawanishi E7K1/E7K2 Alf (designed to be used with AVI Models and Hasegawa kits)
Diorama accessories (resin)
Catalogue
£10.40
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

No Image
Mitsubishi Ki-21-I Otsu 'Sally' (8x camo)
Aircraft decals (military)
Catalogue
£11.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
Mitsubishi G4M1 Betty in Kumogata scheme (9x
Aircraft decals (military)
Catalogue
£11.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
Mitsubishi Ki-21-Ia (designed to be used with ICM kits)[Ki-21-Ib Ki-21-Ic]
Aircraft paint masks (self adhesive)
Catalogue
£11.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
Tainan Kaigun Kokutai (8x camo) Decal set including 8 camouflage schemes: Mitsubishi A6M2 Zero Reisen 21 "V-103", m/n unknown, FPO1c Sakai Saburo Reisen 21 "V-141", m/n unknown, Houkoku 439, FPO2c Arita Yoshisuke Reisen 21 "V-103", m/n 3647, Houkoku 554, FPO1c Sakai Saburo Reisen 21 "V-110", m/n 5374, Houkoku 500, Lae airbase Reisen 21 "V-126", m/n 642, FPO3c Honda Toshiaki Reisen 21 "V-153", m/n 2326, Lt. Kawai Shir...
Aircraft decals (military)
Catalogue
£12.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

No Image
1:48
Black Checker Tail Sqdr. (4x camo) Decal set including 4 camouflage schemes Republic P-47D Thunderbolts of the 346th FS, 350th FG, 12th AF over Italy: P-47D-26-RA "6A2", s/n 42-28326, named "Wave of Destruction", pilot Lt. Martin Domin P-47D-26-RA "6A3", s/n 42-28327, named "Iron Ass" / "Louisiana Lady" P-47D-28-RA "6A7", s/n 42-28619, named "Blue Bonnet Belle II" / "Joan Gail", pilot Lt. Brazil P-47D-27-RA "6B1", s/n...
Aircraft decals (military)
Catalogue
£12.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

The Burma Banshees Curtiss P-40N-1 and P-40N-5 of the 80th Fighter Group over Assam, India. Includes decals for 7 aircraft: P-40N-5 "White1", s/n 42-2105264, Col. Ivan E. McElroy, the 80th Fighter Group commander (from July 1943 to April 1944) P-40N-1 "White 15", s/n unknown, 1st Lt. Robert Gale, 88th FS, 80th FG, Nagaghuli, Assam, India, Spring 1944 P-40N-1 "White 44", s/n 42104590, "Lulu Belle", 2nd Lt. Philip R. Adair, ...
Aircraft decals (military)
Catalogue
£11.30
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

No Image
Tainan Kaigun Kokutai (17 x camo) Mitsubishi A6M2 Zero Reisen 21 "V-103", m/n 3647, Houkoku 554, FPO1c Sakai Saburo Reisen 21 "V-117", m/n 2641, Houkoku 529, Lt. Yamashita Joji Reisen 21 "V-126", m/n 642, FPO3c Honda Toshiaki Reisen 21 "V-152", m/n 2576 or 2586 or, 4578, FPO3c Goto Tatsusuke Reisen 21 "V-157", m/n 5374, captured at Lae, New Guinea Reisen 21 "V-172", m/n 578, captured at Gasmata, New Britain Reisen 21 ...
Aircraft decals (military)
Catalogue
£12.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

No Image
Black Checker Tail Sqdr. (6x camo) Decal set including 6 camouflage schemes Republic P-47D Thunderbolts of the 346th FS, 350th FG, 12th AF over Italy: P-47D-26-RA "6A2", s/n 42-28326, named "Wawe of Destruction", pilot Lt. Martin Domin P-47D-26-RA "6A3", s/n 42-28327, named "Iron Ass" / "Louisiana Lady" P-47D-30-RA "6A6", s/n 42-28326, named "Here Comes Elmer Now", pilot Capt. Belcher P-47D-28-RA "6A7", s/n 42-28619,...
Aircraft decals (military)
Catalogue
£11.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
Mitsubishi A6M2 Zero fighters (6xcamo) A6M2 model 21 "81-1183", 381st Kokutai, Sorong airbase, Jefman Island, May 1944 A6M2 model 21 s/n 5404, "X-183", 3rd Kokutai, Koepang airfield, Timor, March 1942 A6M2 Model 21 s/n 5649, "K-108", Kanoya Kokutai, Lakunai airbase, Rabaul, New Britain, October 1942 A6M2 Model 21 "741-37", 12th Koku Kantai Headquarters Fighter Unit, Matsuyama airbase, Ehime pref., March 1944 A6M2 Model 21...
Aircraft decals (military)
Catalogue
£12.20
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

No Image
Mitsubishi A6M2/3 Zero fighters (6xcamo) A6M2 model 21 "43-179", 343rd Kokutai "Hayabusa" (Peregrine falcon), Tinian Island, Northern Mariana Islands, April 1944 A6M3 model 22 "1-114", Rabauru Kokubuntai (Rabaul Air Force), Lanukanai airbase, fall 1943 A6M3 Model 22 "7-101", Rabauru Kokubuntai (Rabaul Air Force), Lanukanai airbase, early 1944 A6M3 Model 22 "158", 251st Kokutai, Lanukanai airbase, mid 1943 A6M2 Model 21 "...
Aircraft decals (military)
Catalogue
£12.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

No Image
"Kyoiku Kokutai" Unknown and Less Known Units of the Japanese Imperial Army and Navy Pt.VI x 4 schemes Curtiss P-40E Kittyhawk "Kyo-511", Atsugi airfield Aichi D3A2 'Val' "Kyo-241", Atsugi airfield Mitsubishi G3M2/G3M3 Nell "Kyo-1303" Mitsubishi G4M1 'Betty' "Kyo2-302", Atsugi airfield
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

No Image
Japanese Navy Reconnaissance Seaplane Nakajima E8N1/E8N2 "Dave" includes 4 camouflage schemes: E8N2 "5-8" and later "V-8", Seaplane Tender Chiyoda, Houkoku No.165 "Kamaishi Go" E8N1 "NaChi-4", Heavy Cruiser Nachi, Koyfu, April 1936, Japan E8N1 "YoHa-91", Yokohoma Kokutai, Yokohoma Airfield, Kanagawa Prefecture, Japan E8N2 "1-5", Armored Cruiser Izumo, Houkoku No.208 "Dai Ni Chosen Bei Koku Go", Shanghai, China 1939
Aircraft decals (military)
Catalogue
£11.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
Japanese Navy Reconnaissance Seaplane Nakajima E8N1/2 Includes 8 camouflage schemes: E8N2 "I-5", Armored Cruiser Izumo, Houkoku No.208 "Dai Ni Chosen Bei Koku Go", Shanghai, China, 1939 E8N2 "AI-2", battleship Nagato, Houkoku No.352 "Dai Tokyo Tabako Go", Setonaikai (Seto Inland Sea), October 1941 E8N1 "NaChi-4", Heavy Cruiser Nachi, Kyushu, Japan, April 1936 E8N1 "5-1", Seaplane Tender Kamoi, East China Sea, June-July 19...
Aircraft decals (military)
Catalogue
£11.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
Mitsubishi Ki-15 "Babs" Unknown and Less Known Units of the Japanese Imperial Army and Navy Pt.IV Includes one camouflage scheme Ki-15-I of the IJA unidentified unit, probably Dokuritsu Hiko 16 Chutai, date and location not available
Aircraft decals (military)
Catalogue
£7.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

1:72
Donated Birds Pt.III - Japanese Navy Aircraft with "Houkoku" Inscriptions Includes Mitsubishi A5M4, Nakajima B5N1, Kawanishi E7K1, Nakajima E8N1 Includes decals for 8 aircraft: A5M4 "S-160", Houkoku 367 (No.2 Tokyo Paper Commerce), Citose Kokutai, Chitose Airbase, Hokkaido, Japan, December 1940 - May 1941 A5M4 "S-153", Houkoku 373 (Isetan) A5M4, Houkoku 278 (Osaka Gas) A5M4 "9-158", Houkoku 278 (Osaka Gaz), the same air...
Aircraft decals (military)
Catalogue
£11.30
-
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
Donated Birds Pt.IV - Japanese Army Aircraft with Patriotism Inscriptions "Aikoku" Includes decals for 9 aircraft: Nakajima Ki-27, Aikoku 397 (Nagoya Shibuya Ward Citizens/Hiroyuki Electric Co., Ltd), Akeno Rikugun Hiko Gakko, Mie Prefecture, Japan Nakajima Ki-27, Aikoku 645 (Zenitaka), Akeno Rikugun Hiko Gakko, Mie Prefecture, Japan Mitsubishi Ki-51, Aikoku 569 (Kyushu Cpnfectionery), Hiko Dai 44 Sentai, China Area, 19...
Aircraft decals (military)
Catalogue
£11.30
Out of Stock
0