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=2630
		 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 18 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
MiniArt MT38033
GERMAN INDUSTRIAL TRACTOR D8511 MOD. USUALLY é£57.99. TEMPORARILY SAVE 1/3RD!!! 1936 WITH CARGO TRAILER HIGHLY DETAILED MODELS KIT CONTAINS MODELS OF: TRACTOR, CARGO TRAILER, 4 CABLE SPOOLS, 1 FIGURE. PHOTO-ETCHED PARTS INCLUDED DECAL SHEET INCLUDED CLEAR PARTS INCLUDED
Civilian vehicles kits (injection)
Special Offers
£38.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
MiniArt MT38059
TEMPO E400 STAHLBLECHPRITSCHE 3-WHEEL TRUCK USUALLY é£42.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 5 OPTIONS DOORS CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION
Civilian vehicles kits (injection)
Special Offers
£28.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
Revell RV7822
Fendt F20 Dieselroƒlž. WAS é£32.49. TEMPORARILY SAVE 1/3RD!!! The very first tractors developed by Johann Georg Fendt were called Dieselroƒlž. The diesel-powered F20 series was particularly successful, with almost 9,000 units built between 1951 and 1957. This laid the foundation for what Fendt is today: one of the most successful tractor manufacturers in the world. Rotating wheels Movable engine hood Thanks to the innovativ...
Civilian vehicles kits (injection)
Special Offers
£21.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
MiniArt MT35371
TEMPO E400 HOCHLADER PRITSCHE. GERMAN 3-WHEEL DELIVERY TRUCK WAS é£42.99. TEMPORARILY SAVE 1/3RD!!!
Civilian vehicles kits (injection)
Special Offers
£28.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
MiniArt MT38032
TEMPO A400 ATHLET 3-WHEEL DELIVERY TRUCK WAS é£42.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 5 OPTIONS DOORS CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION
Civilian vehicles kits (injection)
Special Offers
£28.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
MiniArt MT38043
GERMAN CARGO TRAILER with 6 DECAL OPTIONS WAS é£26.99. TEMPORARILY SAVE 1/3RD!!!
Civilian vehicles kits (injection)
Special Offers
£17.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
MiniArt MT38047
TEMPO E400 KASTENWAGEN 3-WHEEL DELIVERY BOX TRACKWAS é£42.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 4 OPTIONS DOORS CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION
Civilian vehicles kits (injection)
Special Offers
£28.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
MiniArt MT38049
EMPO A400 LIEFERWAGEN. VEGETABLE DELIVERY VAN WAS é£42.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT CLEAR PLASTIC PARTS INCLUDED PHOTO-ETCHED PARTS INCLUDED DECAL SHEET FOR 3 OPTIONS DOORS CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION
Civilian vehicles kits (injection)
Special Offers
£28.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
MiniArt MT38061
U.S. TOW TRUCK G506 WAS é£52.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED MODEL BOX CONTAIN UNASSEMBLED PLASTIC MODEL OF TRUCK WITH 3 TON SERVICE CRANE DOORS, CAR HOOD CAN BE POSED OPEN OR CLOSED ENGINE ACCURATELY REPRESENTED CLEAR PLASTIC PARTS INCLUDED PHOTOETCHED PARTS INCLUDED 4 DECAL OPTIONS
Civilian vehicles kits (injection)
Special Offers
£35.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
MiniArt MT38064
1,5t 4x4 G506 CARGO TRUCK WAS é£52.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT PHOTO-ETCHED PARTS INCLUDED CLEAR PLASTIC PARTS INCLUDED DECAL SHEET FOR 5 OPTIONS DOORS AND HOOD CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION HIGHLY DETAILED ENGINE COMPARTMENT,TRANSMISSION,BRAKE, STEERING SYSTEMS BENCH POSITION FOLDED OR UNFOLDED
Civilian vehicles kits (injection)
Special Offers
£35.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
MiniArt MT38067
U.S. STAKE BODY TRUCK G506 WAS é£52.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT PHOTO-ETCHED PARTS INCLUDED CLEAR PLASTIC PARTS INCLUDED DECAL SHEET FOR 4 OPTIONS DOORS AND HOOD CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION HIGHLY DETAILED ENGINE COMPARTMENT, TRANSMISSION, BRAKE, STEERING SYSTEMS
Civilian vehicles kits (injection)
Special Offers
£35.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
ICM ICM35598
G7107 US Cargo Truck with or without awning. Choice of license plate numbers WAS é£35.80. TEMPORARILY SAVE 1/3RD!!!
Civilian vehicles kits (injection)
Special Offers
£23.87
-
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

MiniArt MT38016
GERMAN CAR 170V CABRIO SALOON WAS é£36.99. TEMPORARILY SAVE MORE THAN 1/3RD!!! HIGHLY DETAILED MODEL ALL DOORS CAN BE POSED OPEN & CLOSED PHOTO-ETCHED PARTS INCLUDED CLEAR PARTS INCLUDED DECAL SHEET FOR 6 MARKING OPTIONS 2 FIGURES INCLUDED LUGGAGE INCLUDED
Civilian vehicles kits (injection)
Special Offers
£24.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
MiniArt MT38023
GERMAN TRUCK L1500S with CARGO TRAILER WAS é£64.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT DOORS CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION HIGHLY DETAILED ENGINE COMPARTMENT, TRANSMISSION, BRAKE, STEERING SYSTEMS PHOTO-ETCHED PARTS INCLUDED CLEAR PLASTIC PARTS INCLUDED BOX CONTAINS MODELS OF: CARGO TRUCK, TRAILER, WOODEN BARRELS DECAL SHEET FOR 2 OPTIONS
Civilian vehicles kits (injection)
Special Offers
£43.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
MiniArt MT38055
EUROPEAN AGRICULTURAL TRACTOR WITH CART WAS é£57.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED MODELS KIT CONTAINS MODELS OF: TRACTOR, FARM CART, 2 FIGURES & VILLAGE ACCESSORIES. TWO BUILD-UP OPTIONS PHOTO-ETCHED PARTS INCLUDED DECAL SHEET INCLUDED September 2022 release!
Civilian vehicles kits (injection)
Special Offers
£38.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
MiniArt MT38056
1:35
US 1,5t 4x4 G506 FLATBED TRUCK WAS é£52.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED PLASTIC MODEL KIT PHOTO-ETCHED PARTS INCLUDED CLEAR PLASTIC PARTS INCLUDED DECAL SHEET FOR 5 OPTIONS DOORS AND HOOD CAN BE ASSEMBLED IN OPEN OR CLOSED POSITION HIGHLY DETAILED ENGINE COMPARTMENT,TRANSMISSION, BRAKE, STEERING SYSTEMS. 10 BARRELS ARE INCLUDED DRIVER FIGURE IS INCLUDED
Civilian vehicles kits (injection)
Special Offers
£35.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
ICM ICM35605
Model T 1914 Fire Truck, American Car WAS é£25.80. TEMPORARILY SAVE 1/3RD!!!
Civilian vehicles kits (injection)
Special Offers
£17.20
-
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
MiniArt MT38038
GERMAN TRACTOR D8506 WITH TRAILER WAS é£49.99. TEMPORARILY SAVE 1/3RD!!! HIGHLY DETAILED MODELS KIT CONTAINS MODELS OF: TRACTOR, CARGO TRAILER, 12 CANS, 7 WOODEN BARRELS, 9 BAGS, 1 FIGURE. TWO BUILD-UP OPTIONS PHOTO-ETCHED PARTS INCLUDED DECAL SHEET INCLUDED CLEAR PARTS INCLUDED
Civilian vehicles kits (injection)
Special Offers
£33.33
-
In Stock
5+


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