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_category_id = 114 
		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
Special Offers | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Special Offers



Found 2,274 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

Scale

Product Type

Manufacturer

Added Within

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

Sukhoi Su-35 Flanker-E Air intakes (designed to be used with Kitty Hawk Model kits) WAS é£30.25. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (etched)
Special Offers
£19.99
-
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

1000 Lbs Handtruck WAS é£10.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Diorama accessories (resin)
Special Offers
£6.93
-
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

No Image
Boeing CH-47A Chinook WAS é£82.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft kits (injection)
Special Offers
£55.33
-
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
Pz.Beib.Wg V Ausf.A Panther WAS é£60.99. TEMPORARILY SAVE 1/3RD!!! is the common name of a medium tank fielded by Nazi Germany in World War II that served from mid-1943 to the end of the European war in 1945. The Panther's excellent combination of firepower, mobility, and protection served as a benchmark for other nations' late war and immediate post-war tank designs, and it is frequently regarded as one of the best tank desig...
Military vehicle kits (injection)
Special Offers
£40.66
-
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
Leopard C2 MEXAS with TWMP WAS é£62.99. TEMPORARILY SAVE 1/3RD!!! To enable the Canadian Leopard C2 MBTs to operate the dozer blade as well as the mine plough and mine roller systems, an Engineer Equipment Interface Modification Kit (EEJM) has been developed by Krauss-Maffei. This allows the tank to operate the dozer blade or one of two mine clearing systems, permitting operation with the driver's hatch closed down. The exteri...
Military vehicle kits (injection)
Special Offers
£41.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
ITALIAN TRAFFIC SIGNS 1930-40's WAS é£13.99. TEMPORARILY SAVE 1/3RD!!! UNASSEMBLED PLASTIC MODEL KIT BOX CONTAINS MODELS OF TRAFFIC SIGNS DECALS ARE INCLUDED
Diorama accessories (injection)
Special Offers
£7.77
-
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
Panther Ausf. D Gift Set WAS é£48.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£32.66
-
In Stock
10+

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
Kubelwagen Typ 82 LIMITED EDITION WAS é£229.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£153.33
-
In Stock
10+

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
Gift Set - Audi R10 TDI Le Mans & 3D Puzzle WAS é£61.99. TEMPORARILY SAVE 1/3RD!!!
Car kits (injection)
Special Offers
£41.33
-
In Stock
10+

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
Boba Fetts Starship WAS é£42.49. TEMPORARILY SAVE 1/3RD!!!
Film, Science Fiction or TV related
Special Offers
£28.33
-
In Stock
10+

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:25
Mercedes 1625 TLF 24/50 Ziegler WAS é£78.99. TEMPORARILY SAVE 1/3RD!!!
Fire engine kits (Injection)
Special Offers
£52.66
-
In Stock
9

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:32
KISS Tour Truck Gift Set WAS é£69.99. TEMPORARILY SAVE 1/3RD!!!
Truck and trailer kits (injection)
Special Offers
£46.66
-
In Stock
10+

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
Porsche Junior 108 WAS é£31.49. TEMPORARILY SAVE 1/3RD!!! Farming Simulator Edition Exclusive download code for the virtual Porsche Junior tractor included (download free of charge. Farming Simulator must be purchased separately)
Truck and trailer kits (injection)
Special Offers
£20.99
-
In Stock
9

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

Lockheed P-38J/P-38L Lightning (designed to be used with Minicraft kits) WAS é£13.99. TEMPORARILY SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£9.32
-
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

Focke-Wulf Fw-190F-8 cockpit detail set (designed to be used with Tamiya kits) WAS é£11.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£7.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

Messerschmitt Bf-109G-5/Bf-109G-6 (designed to be used with Hasegawa kits) WAS é£11.99. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft detailing sets (resin)
Special Offers
£7.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
Bantam BRC-40 4x4 WAS é£69.99. TEMPORARILY SAVE 1/3RD!!!
Car kits (injection)
Special Offers
£46.68
-
In Stock
10

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
.50 Cal Body and Handgrip WAS é£6.80. NOW BEING CLEARED!! SAVE 1/3RD!!!
Diorama accessories (resin)
Special Offers
£4.53
-
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

No Image
.50 Cal Hangrip only WAS é£5.30. NOW BEING CLEARED!! SAVE 1/3RD!!!
Diorama accessories (resin)
Special Offers
£3.53
-
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

Lt.L.Surzenko Polikarpov I-16 Decals WAS é£4.40. NOW BEING CLEARED!! SAVE 1/3RD!!!
Aircraft decals (military)
Special Offers
£2.93
-
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

No Image
Bell UH-1 Air Ambulance in Vietnam War Part 2 WAS é£17.99. TEMPORARILY SAVE 1/3RD!!! 1. UH-1H,(Flower Power) 66-16429, 45th Medical Company, Lai Khe, 1968. 2. UH-1H,(Bandaid Bandit) 70-15769, 571th Medical Company, Phu Bai, 1972. 3. UH-1H,(Woodstock) 68-15230, 57th Medical Company, Phu Bai, 1971. 4. UH-1H,(Iron Butterfly) ,69-15238, 498th Medical Company, Pleiku, 1970.
Aircraft decals (military)
Special Offers
£11.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
Bristol Beaufighter TF Mk X Part 3 WAS é£17.30. TEMPORARILY SAVE 1/3RD!!! 1 Beaufighter TF.Mk.X. Unit: 254 Sqn, Coastal Command RAF. Serial: QM-F (Rd351) RAF Coastal Command, mid-1944.
Aircraft decals (military)
Special Offers
£11.53
-
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
Douglas SBD Dauntless & Banshee in Combat. Part 4 WAS é£17.30. TEMPORARILY SAVE 1/3RD!!! 1 A-24B-1-DO (SBD-5) 42-54298 "Bar Fly" 407th Bomb Group (Dive) Amchitka, Territory of Alaska July-August 1943. 2 SBD-5, "White 51", NZ 5051, flown by F/Lt. J. W. Edwards & F/Sgt. L. A. Hoppe, No 25 Dove Bombing Squadron RNZAF, Piva, Bougainville, April-May 1944.
Aircraft decals (military)
Special Offers
£11.53
-
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
K-type Blimp (K-3/6/28) 'Early Production' USUALLY é£19.70. TEMPORARILY SAVE 1/3RD!!! Non-rigid airships (or blimps) were the first types of lighter-than-air craft. Since the turn of the 20th century, they have been employed on a large scale by armed forces and also engaged commercially. The Goodyear Aircraft Company, the foremost producer of airships in the United States, produced a series of K-class airships which became ...
Aircraft kits (injection)
Special Offers
£13.13
-
In Stock
10+

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
K-type Blimp (K-74/112/134) 'Fleet Defender' USUALLY é£19.70. TEMPORARILY SAVE 1/3RD!!! Non-rigid airships (or blimps) were the first types of lighter-than-air craft. Since the turn of the 20th century, they have been employed on a large scale by armed forces and also engaged commercially. The Goodyear Aircraft Company, the foremost producer of airships in the United States, produced a series of K-class airships which becam...
Aircraft kits (injection)
Special Offers
£13.13
-
In Stock
10+