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=1865 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 37 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
Blackdog BDT35261
German winter clothes WWII
Diorama accessories (resin)
New Arrivals
25/04/2024
£22.30
£20.07
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 GE48084
Sepecat Jaguar tow bars wheels up & down - 3D-printed (designed to be used with Airfix and Heller kits)
Diorama accessories (resin)
New Arrivals
16/04/2024
£14.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
AIM - Ground Equipment GE48142
Leyland Subsidy A type flatbed lorry - circa 1916 - 3D-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£23.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
AIM - Ground Equipment GE32084
Sepecat Jaguar tow bars wheels up & down - 3D-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£19.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
AIM - Ground Equipment GE48139
1:48
Huck starteré - RFC circa 1918 - 3D-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£38.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
AIM - Ground Equipment GE48140
Clayton Crawler tractor - RFC circa 1917 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£30.60
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 GE48141
Leyland Subsidy A type fuel lorry - circa 1916 - 3D-Printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£27.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
AIM - Ground Equipment GE72139
1:72
Huck starteré - RFC circa 1918 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£21.60
£19.44
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 GE72140
1:72
Clayton Crawler tractor - RFC circa 1917 - 3d-printed
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
AIM - Ground Equipment GE72141
Leyland Subsidy A type fuel lorry - circa 1916 - 3d-printed
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
AIM - Ground Equipment GE72142
Leyland Subsidy A type flatbed lorry - circa 1916 - 3d-printed
Diorama accessories (resin)
New Arrivals
16/04/2024
£16.20
£14.58
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
Plus Model PMAL3021
Set includes two hand-pull bomb carts with 250 Lb. bombs and decals. Made with 3D printing technology
Diorama accessories (resin)
New Arrivals
16/04/2024
£15.60
£14.04
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
Plus Model PMAL4113
Set includes two hand-pull bomb carts and two 250 Lb bombs with decals. Made with 3D technology.
Diorama accessories (resin)
New Arrivals
16/04/2024
£10.30
£9.27
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
Plus Model PMDP3035
1:35
The set includes two fully equipped machine guns. Made by 3D printing. Glue and paints are not included.
Diorama accessories (resin)
New Arrivals
16/04/2024
£12.99
£11.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
Plus Model PMDP3037
US Jerry cans WWII
Diorama accessories (resin)
New Arrivals
16/04/2024
£10.99
£9.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
Plus Model PMDP3038
US Jerry cans WWII with holder bases
Diorama accessories (resin)
New Arrivals
16/04/2024
£12.99
£11.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
Art Scale 200-A72013
Womens Bicycle classic 3D-Printed
Diorama accessories (resin)
New Arrivals
11/04/2024
£7.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
AIM - Ground Equipment GE72113
RE-RELEASED! crew access steps for the North-American XB-70 Valkyrie (designed to be used with AMT, Esci and Italeri kits) 3D-Printed
Diorama accessories (resin)
New Arrivals
24/03/2024
£21.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
ResKit RSU32-0098
Lockheed-Martin F-35A Lightning II access ladder (designed to be used with Academy, Italeri and Tamiya kits) (3D-Printed)
Diorama accessories (resin)
New Arrivals
22/03/2024
£12.70
£11.43
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
Blackdog BDT35258
British army clothes WWII
Diorama accessories (resin)
New Arrivals
05/03/2024
£18.80
£16.92
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
Art Scale 200-A72004
Goryunov SG-43 machine gun, 3D printing, includes: - 1 pc Goryunov SG-43 machine gun - 1 pc machine gun carriage - 2 pc wheel - 1 pc machine gun shield
Diorama accessories (resin)
New Arrivals
28/02/2024
£8.40
Out of Stock
0

   1   |   2      »      [2]

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