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 pt.id=1821 AND P.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 264 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
Delivered 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
Eduard kits EDK11181
OVERLORD: D-DAY MUSTANGS. This is a new tool Limited Edition, Dual Combo kit featuring the P-51B Mustang in 1/48 scale. kits) The kit will include decals for 10 machines that participated in the 1944 Normandy landings, as well as pre-painted photo-etched parts and masks.
Aircraft kits (injection)
New Arrivals
02/05/2024
£53.50
£48.15
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
Eduard kits EDK7473
Supermarine Spitfire Mk.IXc late 1/72 EDUARD-WEEKEND The Weekend edition kit of British fighter aircraft Spitfire Mk.IXc late in 1/72 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: no - resin parts: no kits)
Aircraft kits (injection)
New Arrivals
02/05/2024
£11.99
£10.79
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
Eduard kits EDK84199
Supermarine Spitfire Mk.IXc late The Weekend edition kit of British fighter aircraft Spitfire Mk.IXc late in 1/48 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: no - resin parts: no kits)
Aircraft kits (injection)
New Arrivals
02/05/2024
£19.60
£17.64
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
Eduard kits EDR0019
North-American P-51B Mustang EDUARD-ROYAL CLASS The Royal Class edition kit of the famous US fighter aircraft P-51B Mustang in 1/48 scale. The kit offers markings for 14 aircraft; 13 of them of US Air Force and one Mustang Mk.III of the RAF. The kit is prepared as a Dual Combo, it is extended version of kit 11181. Both kits will feature different packaging designs and box art. This kit will only be produced in numbers ac...
Aircraft kits (injection)
New Arrivals
02/05/2024
£79.99
£71.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

No Image
Academy AC12334
North-American PBJ-1D (B-25 Mitchell), ca.1944 United States Marine Corps and Navy twin-engine medium bomber produced by North-American Aviation. A variant of the USAAF B-25 Mitchell, the PBJ-1D was introduced in 1942 and saw service in the Pacific War. Powered by four Pratt & Whitney R-2600-13 engines with a maximum speed of 330mph and a range of 1,600 miles. The PBJ-1D was armed with a nose-mounted 7.62 mm machine gun, a do...
Aircraft kits (injection)
New Arrivals
01/05/2024
£31.99
£28.79
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
Academy AC12357
Ilyushin Il-2M3 "Berlin 1945" The Ilyushin Il-2M3 Shturmovik (or Stormovik) was a Soviet ground-attack aircraft that played a major role in the Red Army's victory in the war on the Eastern Front in World War II. Nicknamed the "Black Widow" or "Flying Tank", the Il-2M3 was used in all major Soviet offensives during World War II, and it is estimated that it destroyed or damaged more than 50,000 German tanks, armoured vehicles a...
Aircraft kits (injection)
New Arrivals
01/05/2024
£28.99
£26.09
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
Academy AC12584
Consolidated B-24H Liberator "Zodiac" Heavy four-engined bomber used by the United States Army Air Forces in World War II. It was one of the most versatile and widely produced bombers of the war. The Liberators represented by the markings with this kit, "Gemini" and "Libra", were part of a group of Zodiac-named bombers that served with the 486th Bombardment Group, 834th Bombardment Squadron, and participated in numerous bombi...
Aircraft kits (injection)
New Arrivals
01/05/2024
£22.99
£20.69
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
Academy AC12585
KAI KF-21 "Boramae" multirole fighter, ca.2022/23 4.5 generation twin-engine canard-delta stealth aircraft in development by Korea Aerospace Industries (KAI) for the Republic of Korea Air Force (ROKAF). "Boramae" translates to "young hawk". No glue required. Can be built landing or flying. Display stand included. Choice of markings (stickers) for 4 prototypes: (1) ROKAF KF-21 flight test prototype 001, Sacheon, 2022; (2) R...
Aircraft kits (injection)
New Arrivals
01/05/2024
£18.99
£17.09
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
Academy AC12629
Boeing E-3G Sentry AEW&C, ca.2023 Airborne Early Warning and Control aircraft (AEW&C) converted from the Boeing 707-320 commercial airliner featuring a characteristic rotating radar dome (rotodome), providing airborne radar surveillance, command, and control. In operation with the United States Air Force, NATO member nations and other countries. The observation system designed by Boeing is known as Airborne Warning and Contro...
Aircraft kits (injection)
New Arrivals
01/05/2024
£22.99
£20.69
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
Academy AC12634
Douglas C-118 Liftmaster Four-engine, turboprop-powered heavy transport aircraft derived from the Douglas DC-6 commercial airliner. Developed by the Douglas Aircraft Company and used by the United States Air Force from 1955 to 1971. Reboxed Minicraft kit with new decals. Choice of 7 markings: (1) Military Air Transport Service (MATS) C-118A, USAF, early 1960s; (2) US Navy R6Ds of Fleet Logistics Support Reserve Squa...
Aircraft kits (injection)
New Arrivals
01/05/2024
£19.99
£17.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

No Image
Karaya KY144-23
Embraer ERJ-170 Air France HOP! (ex-Hasegawa)
Aircraft kits (injection)
New Arrivals
26/04/2024
£30.40
£27.36
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
Karaya KY144-25
Embraer ERJ-170 Delta Connection/American Eagle (ex-Hasegawa)
Aircraft kits (injection)
New Arrivals
26/04/2024
£30.40
£27.36
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
Karaya KY144-34
Lockheed L-10A Electra
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.29
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
Karaya KY144-35
1:144
Lockheed L-10A Electra RAF
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.29
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
Karaya KY144-36
1:144
Lockheed L-10 Electra early Pan Am
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.29
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
Karaya KY144-37
1:144
Lockheed L-10A Electra USN/USCG
Aircraft kits (injection)
New Arrivals
26/04/2024
£16.99
£15.29
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
Karaya KY144-45
1:144
Dash 8 Q400 - (PLL LOT) - plastic parts AMP UA (ex-EE) + silk/digital printed decals.
Aircraft kits (injection)
New Arrivals
26/04/2024
£23.99
£21.59
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

Hobby 2000 H2K72014
1:72
Douglas SBD-4/SBD-5 Dauntless (ex Hasegawa)
Aircraft kits (injection)
New Arrivals
24/04/2024
£20.99
£18.89
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
Hobby 2000 H2K72071
1:72
Junkers Ju-87G-2 The Last Flight ACADEMY + CARTOGRAF + MASKS
Aircraft kits (injection)
New Arrivals
24/04/2024
£13.99
£12.59
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
Hobby 2000 H2K72072
1:72
Junkers Ju-87G-2 'Stuka' Eastern Front 1944 + CARTOGRAF + MASKS
Aircraft kits (injection)
New Arrivals
24/04/2024
£13.99
£12.59
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
Hobby 2000 H2K72073
Vought F-8E Crusader "MiG Killers" ACADEMY + CARTOGRAF + MASKS
Aircraft kits (injection)
New Arrivals
24/04/2024
£17.80
£16.02
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
Hobby 2000 H2K72074
1:72
Vought F-8E Crusader "Marines" ACADEMY + CARTOGRAF + MASKS
Aircraft kits (injection)
New Arrivals
24/04/2024
£17.80
£16.02
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
Hobby 2000 H2K72075
Heinkel He-111D HASEGAWA + CARTOGRAF + MASKS + 3D PRINT
Aircraft kits (injection)
New Arrivals
24/04/2024
£35.80
£32.22
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
Hobby 2000 H2K72076
Heinkel He-111P Outbreak of War 1939 HASEGAWA + CARTOGRAF + MASKS
Aircraft kits (injection)
New Arrivals
24/04/2024
£33.80
£30.42
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
Hobby 2000 H2K72077
Heinkel He-111P Western Campaign 1940 HASEGAWA + CARTOGRAF + MASKS + 3D PRINT
Aircraft kits (injection)
New Arrivals
24/04/2024
£34.99
£31.49
Out of Stock
0

   1   |   2   |   3   |   4   |   5      »      [11]

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