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=7888
		 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 13 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
AFV Club AF35291
M113A1 LRV in Vietnam War USUALLY é£79.99. TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£53.33
-
In Stock
20+

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
AFV Club AF35299
1:35
US Propellant Containers for M109 A1-A4 SPG Upgrade track set for 1/35 scale AFV Club M109 Self-propelled Howitzer types up to M109A4. Decals included. USUALLY é£17.99 TEMPORARILY SAVE 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£11.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

No Image
AFV Club AF35330
1:35
M109G 155mm/L23 German Self-propelled Howitzer USUALLY é£73.99. TEMPORARILY HALF PRICE!!!
Military vehicle kits (injection)
Special Offers
£36.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

No Image
AFV Club AF35341
British Small Box Girder Bridge WWII assault bridge emplaced by British Army Royal Engineers during the Normandy Landings in June 1944. Weighing 4 tons and rated up to 40 tons capacity, the 34ft-long tank-transportable bridge conveyed heavy Allied vehicles across gaps up to 30ft (e.g. bomb crater, anti-tank ditch), or up and over a seawall. USUALLY é£57.99. TEMPORARILY SAVE 1/3RD!!! Includes bridge parts common in Churchill ...
Military vehicle kits (injection)
Special Offers
£38.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
AFV Club AF35354
Husky Mk.III Vehicle Mounted Mine Detector with interrogation arm WAS é£87.99. TEMPORARILY SAVE MORE THAN 1/3RD!!!
Military vehicle kits (injection)
Special Offers
£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

No Image
AFV Club AF35S93
Patriot Launching Station & PAC-3 M91 Munition WAS é£149.99. TEMPORARILY SAVE 1/3RD!!! Reboxed Trumpeter kit with new resin & photo-etch parts and decals for US Army & RoCAF
Military vehicle kits (injection)
Special Offers
£99.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

No Image
AFV Club AF70006
1:700
USS Oliver Hazard Perry Class Frigate Lead ship of the 1970s Perry class of guided missile frigates USUALLY é£47.99. TEMPORARILY SAVE 1/3RD!!!
Ship kits, waterline hull (injection)
Special Offers
£31.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

No Image
AFV Club AF73517
USA Landing Vehicle Set 2 USUALLY é£21.99. TEMPORARILY SAVE 1/3RD!!! Korean War & WW2 New tooling. Set comprises 2-each GMC 353 Gas Tanker, M24 Chaffee, M19 Multiple GMC, M26 Pershing, M4 HST, M8 Light Armoured Car, M1 203mm Howitzer (transit), M1 203mm Howitzer (firing position), M1 155mm Long Tom (transit), M1 155mm Long Tom (firing position).
Military vehicle kits (injection)
Special Offers
£14.66
-
In Stock
17

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
AFV Club AFAG3556
US M1122 & M454 155mm Artillery Shells (aluminium) USUALLY é£13.99. TEMPORARILY SAVE 1/3RD!!! Ammo used in IDF M109A2 'Rochev' & US M109A6 'Paladin' turreted self-propelled howitzers. 16 aluminium shells of type M454 & M1122 (8 each) plus decals.
Military vehicle detailing sets (metal)
Special Offers
£9.33
-
In Stock
18

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
AFV Club AFAG3557
M982-1A 'Excalibur' 155mm Artillery Shell (aluminium) USUALLY é£25.99. TEMPORARILY SAVE 1/3RD!!! Ammo used in IDF M109A2 'Rochev' & US M109A6 'Paladin' self-propelled howitzers
Military vehicle detailing sets (metal)
Special Offers
£17.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
AFV Club AFAG3560
Metal Barrel for M113 MRV (AF35023) USUALLY é£8.99. TEMPORARILY SAVE 1/3RD!!! Metal barrel upgrade for AFV Club Australian Army M113A1 Mechanised Reconnaissance Vehicle. (designed to be used with AFV Club kits)
Military vehicle gun barrels
Special Offers
£5.99
-
In Stock
11

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

AFV Club AFTW6014
1:35
R.O.C Low Visibility Coating Decals . WAS é£7.99. NOW BEING CLEARED. HALF PRICE!!!
Aircraft decals (military)
Special Offers
£3.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
AFV Club AR48S10
Northrop F-5E Yemen/Taiwan "Peace Bell Program" USUALLY é£54.99. TEMPORARILY SAVE 1/3RD!!! YAR Yemen Arab Republic ("North Yemen") 1962-1990. Amalgamated with South Yemen on 22-05-1990 to form the Republic of Yemen. Early version US-supplied F-5E. Variable-position flaps, gear & intake doors. Colours & markings for joint Saudi Arabia/Taiwan in North Yemen 'Peace Bell Program', Yemen Air Force Sqn 112 serial nos 71772, 71776,...
Aircraft kits (injection)
Special Offers
£36.66
-
In Stock
10+


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