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_manufacturer_id=362889 AND ps.id = 954 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		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
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 26 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
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
Thunder Models THU35105
Bergepanzer Hetzer with 20cm Flak Late Production - 650 plastic parts standard - 700 plastic parts bonus edition) - slide moulded / multi- side slide moulded hull and body - Faithfully reproduced details including the gun and ammo supply sections. - Full interior - Full engine compartment in the Limited Edition box plus PE mudguards and skirts - PE detail sheet alternative for ammo boxes
Military vehicle kits (injection)
Catalogue
£48.50
-
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
Thunder Models THU35105SE
Bergepanzer Hetzer with 20cm Flak Late Production [Limited Bonus Edition] - 650 plastic parts standard - 700 plastic parts bonus edition) - slide moulded / multi- side slide moulded hull and body - Faithfully reproduced details including the gun and ammo supply sections. - Full interior - Full engine compartment in the Limited Edition box plus PE mudguards and skirts - PE detail sheet alternative for ammo boxes
Military vehicle kits (injection)
Catalogue
£59.95
-
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
Thunder Models THU35106
Bergepanzer Hetzer with 2cm Flak early production - 650 plastic parts standard - 700 plastic parts bonus edition) - slide moulded / multi- side slide moulded hull and body - Faithfully reproduced details including the gun and ammo supply sections. - Full interior - Full engine compartment in the Limited Edition box plus PE mudguards and skirts - PE detail sheet alternative for ammo boxes
Military vehicle kits (injection)
Catalogue
£48.50
-
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
Thunder Models THU35210
Morris Bofors C9/B Early A new kit of this famous British WWII Gun Truck 14 plastic sprues with 590 parts clear parts resin cast wheels PE detail sheet 3 marking option decal with instruments placards and ammo boxes markings accurately reproduced to the highest detail standard gun and turntable can be assembled in any position vehicle supports can be assembled in traveling or firing position
Military vehicle kits (injection)
Catalogue
£53.95
-
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

No Image
Thunder Models THU35209
Morris C9/B Bofors Gun Truck 13 plastic sprues with 565 parts including clear parts resin cast wheels PE detail sheet 2 different marking options / decal with instrument placards and ammo box markings accurately reproduced to the highest level of detail gun and turntable can be assembled in any position vehicle supports can be assembled in travelling or firing position
Military vehicle kits (injection)
Catalogue
£53.95
-
In Stock
7

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

Thunder Models THU35001
1:35
U.S. Army tractor Case VAI During World War II Case produced more than 1,3 million howitzer shells, 200 sets of bomber aircraft wings, and 20000 specialized war tractors and engines. From 1941 to 1945 the factory built over 15000 special military tractors in roughly 40 variants, and over 4000 tractor engines for the U.S. Army and the Allies - 70 plastic parts (sprues A, B,) - 4 slide molded silicone tyres - PE deta...
Military vehicle kits (injection)
Catalogue
£26.95
-
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

Thunder Models THU35002
1:35
U.S. Army Loader (bulldozer)During World War II Case produced more than 1,3 million howitzer shells, 200 sets of bomber aircraft wings, and 20000 specialized war tractors and engines. From 1941 to 1945 the factory built over 15000 special military tractors in roughly 40 variants, and over 4000 tractor engines for the U.S. Army and the Allies One of the usual setups was a tractor (commonly SI and VAI models) upgraded with the ...
Military vehicle kits (injection)
Catalogue
£32.50
-
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
Thunder Models THU35007
US Army Armored Tractor
Military vehicle kits (injection)
Catalogue
£32.50
-
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

Thunder Models THU35200
Scammell Pioneer TRMU30/TRCU30 Tank Transporter 30ton
Military vehicle kits (injection)
Catalogue
£69.95
-
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

Thunder Models THU35201
1:35
Scammell Pioneer SV2S Recovery Tractor
Military vehicle kits (injection)
Catalogue
£48.50
-
In Stock
3

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

Thunder Models THU35202
Scammell Pioneer R100 Heavy Artillery Tractor
Military vehicle kits (injection)
Catalogue
£48.50
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

Thunder Models THU35204
1:35
Scammell Pioneer TRMU30 Tractor
Military vehicle kits (injection)
Catalogue
£48.50
-
In Stock
3

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

Thunder Models THU35205
1:35
British 30ton TRCU30 Trailer
Military vehicle kits (injection)
Catalogue
£37.95
-
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

Thunder Models THU35207
1:35
Scammell Pioneer TRMU30/TRCU30 Tank Transporter 30 ton 'Goose Neck'
Military vehicle kits (injection)
Catalogue
£69.95
-
In Stock
7

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

Thunder Models THU35211
1:35
British 7.2inch Howitzer
Military vehicle kits (injection)
Catalogue
£37.95
-
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
Thunder Models THU35212
1:35
British Scammell Pioneer Heavy Artillery Tractor R100 with 7.2 Inch Howitzer
Military vehicle kits (injection)
Catalogue
£69.95
-
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

Thunder Models THU35902
1:35
German Gedeckter Gƒ¼terwagen Gr 15t
Military vehicle kits (injection)
Catalogue
£49.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

Thunder Models THU35100
1:35
Bergepanzer 38 Hetzer Late Limited Edition - 400+ plastic parts (sprues A, B, C, 2xD, F, Z) slide molded, multi-side slide molded hull and body - bonus full detail engine compartment and interior - faithfully reproduced crane and winch - PE detail sheet, scale chain, additional bonus PE sheets with Skirts parts and Mudguards parts - PE color printed instruments Bergepanzer 38(t) Hetzer Late Bergerpanzer 38(t) ...
Military vehicle kits (injection)
Catalogue
£57.99
-
In Stock
7

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

Thunder Models THU35005
U.S. Crawler Sixty 60hp Caterpillar tractor
Military vehicle kits (injection)
Catalogue
£49.99
-
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

Thunder Models THU35102
Bergepanzer 38(t) Hetzer Early version. Kit TM35102 Standard edition - 370+ plastic parts (sprues A, B, C, 2xD, E) slide molded, multi-side slide molded hull and body - faithfully reproduced crane and winch - full interior - PE detail sheet, scale chain - PE color printed instruments Bergerpanzer 38(t) was a recovery vehicle based on Hetzer's base, with lower open-top superstructure, manufactured by BMM company ...
Military vehicle kits (injection)
Catalogue
£47.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

Thunder Models THU35208
Scammell Pioneer TRCU30 Goose Neck Trailer 30t. WWII British Army 30ton semi-trailer
Military vehicle kits (injection)
Catalogue
£35.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

Thunder Models THU35904
1:35
German Ssyl 50t Flat Wagon
Military vehicle kits (injection)
Catalogue
£54.99
-
In Stock
6

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

Thunder Models THU35101
Bergepanzer 38(t) Hetzer Late version. - 350+ plastic parts (sprues A, B, C, 2xD, Z) slide molded, multi-side slide molded hull and body - faithfully reproduced crane and winch - PE detail sheet, scale chain - PE color printed instruments Bergerpanzer 38(t) was a recovery vehicle based on Hetzer's base, with lower open-top superstructure, manufactured by BMM company in Prague. The plans were approved in September 19...
Military vehicle kits (injection)
Catalogue
£47.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

Thunder Models THU35003
Indian 741B US Military Motorcycle WWII
Military vehicle kits (injection)
Catalogue
£29.99
-
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

Thunder Models THU35104
German Gepanzerter Mannschaftstransportwagen KƒĪtzchen Armoured Personnel Carrier
Military vehicle kits (injection)
Catalogue
£47.99
-
In Stock
4

   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