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 ps.id = 952 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 62 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
Tamiya TA24169
1957 Fiat 500F
Car kits (injection)
New Arrivals
25/04/2024
£24.99
£22.49
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

Italeri IT0788
DAF 95 Master Truck The 'Concept 95/380' has been, for some time, the flagship product in the renowned DAF manufacturer's lineup, representing a further development of the Space Cab series. It was recognized by the market as a significant evolution compared to its predecessors. Indeed, numerous technical and aesthetic improvements were developed and adopted, greatly appreciated by users of road industrial vehicles, especially ...
Truck and trailer kits (injection)
New Arrivals
19/04/2024
£59.99
£53.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
Nunu Model Kit NU24032
1:24
Mitsubishi Lancer 2000 turbo Hong Kong and Beijing Rally' 85
Car kits (injection)
New Arrivals
19/04/2024
£52.99
£47.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
Nunu Model Kit NU24041
BMW 320i Macau Guia Race 2001 Winner
Car kits (injection)
New Arrivals
19/04/2024
£54.99
£49.49
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
1ManArmy 24DET012
Junkers Ju-87B-2 'Stuka' (designed to be used with Airfix kits)
Aircraft HD paint masks (self adhesive)
New Arrivals
19/04/2024
£34.99
£31.49
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
LF Models LFMM2414
Junkers Ju-87A 'Stuka' Camouflage Pattern Paint Mask (designed to be used with Trumpeter kits)
Aircraft paint masks (self adhesive)
New Arrivals
19/04/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
Techmod TM24022
Supermarine Spitfire Mk.IXc
Aircraft decals (military)
New Arrivals
18/04/2024
£11.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
Techmod TM24023
Republic P-47D Thunderbolt
Aircraft decals (military)
New Arrivals
18/04/2024
£11.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
Quinta Studio QD24010
Mercedes-Benz 230G 3D-Printed & coloured Interior on decal paper (designed to be used with Esci, Italeri and Revell kits)
3D printed decal
New Arrivals
17/04/2024
£8.60
£7.74
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
Quinta Studio QD24012
Lamborghini Countach 5000 Quattrovalvole 3D-Printed & coloured Interior on decal paper (designed to be used with Aoshima kits)
3D printed decal
New Arrivals
17/04/2024
£8.60
£7.74
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
AIM - Ground Equipment GEL24001
Anlasswagen - Luftwaffe External Power Supply Unit - WWII - 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Messerschmitt Bf-109G, Focke-Wulf Fw-190D-9, Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' Anlasswagen - Luftwaffe External Power Supply Un...
Diorama accessories (resin)
New Arrivals
16/04/2024
£32.40
£29.16
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
AIM - Ground Equipment GEL24002
Olwagen - Luftwaffe Oil Service Cart - WWII 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Messerschmitt Bf-109G, Focke-Wulf Fw-190D-9, Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' Olwagen - Luftwaffe Oil Service Cart - WWII 3d-printed....
Diorama accessories (resin)
New Arrivals
16/04/2024
£27.00
£24.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
AIM - Ground Equipment GEL24003
Ladewagen LWC 500IX - Luftwaffe bomb loader - WWII 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' (designed to be used with the Airfix kits)
Diorama accessories (resin)
New Arrivals
16/04/2024
£14.40
£12.96
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
AIM - Ground Equipment GEL24004
Kettenkrad tractor (Sd Kfz. 2) - Luftwaffe Half-track Tractor - WWII 3d-printed. (designed to be used with the Airfix kits) of the Junkers Ju-87B-2 'Stuka' , and the Trumpeter kits of the Messerschmitt Bf-109G, Focke-Wulf Fw-190D-9, Junkers Ju-87A 'Stuka', Junkers Ju-87B-2 'Stuka', Junkers Ju-87B-2/U4 Stuka, Junkers Ju-87D-5 'Stuka' , Junkers Ju-87R 'Stuka' or Junkers Ju-87G-2 'Stuka' Kettenkrad tractor (Sd Kfz. 2) - Luftwa...
Diorama accessories (resin)
New Arrivals
16/04/2024
£18.00
£16.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
Procal Decals PD24-2303
1:24
Greek restored Supermarine Spitfire Mk.IX IN 4 SCALES also set include decals for propeller blades Some history for plane... Spitfire MJ755 was built at Castle Bromwich, and delivered to No. 33 MU at RAF Lyneham in December 1943, remaining in store until early 1944 when it was allocated to the Middle East Air Force and dispatched by sea to Casablanca, arriving 13th March 1944.aâé-¢ By August 1944, MJ755 had been allocated to 4...
Aircraft decals (military)
New Arrivals
05/04/2024
£12.00
£10.80
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
Meng Model MMCS-008
Audi R8 LMS EVA RT Test Type-01
Car kits (injection)
New Arrivals
02/04/2024
£65.99
£59.39
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
Kelik 3D Decals KS24001
Asphalt road race track Base - Acrylic 3 mm (180 x 357 mm)
Diorama bases (acrylic)
New Arrivals
22/03/2024
£20.60
£18.54
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
Kelik 3D Decals KS24002
1:24
Asphalt road 24 Hours of Le Mans type 1 Base - Acrylic 3 mm (180 x 357 mm)
Diorama bases (acrylic)
New Arrivals
22/03/2024
£20.60
£18.54
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
Kelik 3D Decals KS24003
Asphalt road 24 Hours of Le Mans Turn Base - Acrylic 3 mm (180 x 357 mm)
Diorama bases (acrylic)
New Arrivals
22/03/2024
£20.60
£18.54
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
Kelik 3D Decals KS24004
Asphalt road 24 Hours of Le Mans type 2 Base - Acrylic 3 mm (180 x 357 mm)
Diorama bases (acrylic)
New Arrivals
22/03/2024
£20.60
£18.54
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
Kelik 3D Decals KS24005
1:24
Asphalt road 24 Hours of Le Mans type 3 Base - Acrylic 3 mm (180 x 357 mm)
Diorama bases (acrylic)
New Arrivals
22/03/2024
£20.60
£18.54
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

Italeri IT3645
Back in stock! Mercedes Benz 300SL Gullwing The Mercedes-Benz 300 SL was produced in 1954 by the German car manufacturer and it's a masterpiece on four-wheels for design, elegance and luxury. The 300 SL, where the acronym stands for Sport Leicht, (Sport Light), adopted innovative solutions for the time. The Mercedes 2 door coupe had a tubular frame, a front 2.996 cc longitudinal engine connected to a 4-speed transmission and t...
Car kits (injection)
New Arrivals
18/03/2024
£34.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
TopNotch TNM24-M218
Republic P-47D Thunderbolt "Bubble top" Gabreski HV-A "226418" Camouflage pattern paint mask (designed to be used with kits)
Aircraft paint masks (self adhesive)
New Arrivals
15/03/2024
£14.00
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
Attittude Aviation BUC-24019
Supermarine Spitfire Mk.IXe Royal Norwegian Air Force WWII Vinyl paint mask (designed to be used with Airfix kits)
Aircraft paint masks (self adhesive)
New Arrivals
08/03/2024
£15.99
£14.39
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
Tamiya TA20072
Toyota Celica LB Turbo Gr.5
Car kits (injection)
New Arrivals
07/03/2024
£44.99
£40.49
Out of Stock
0

   1   |   2   |   3      »      [3]

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