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=8219
		 AND P.product_category_id = 108 
		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
Limited Availability | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Limited Availability



Found 10 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

Start LIF18
No Scale
Luftwaffe im Focus Edition No 18 aâé-¢ 50 pages, about 65 photos - thereof 10 in colour, 4 colour profiles, 2 maps, Readerforum aâé-¢ Aircraft in Focus: Me-262 'black D' of 2./KG 51 in February 1945 aâé-¢ Photos with a story: Of "Crabs", "Snakes" and "Dƒ¶deln" (The story of 3. Staffel JGr.10 in 1945, Part 2) aâé-¢ Tail: Ship killer in the Arctic Sea -Lt. Hennemann 2./KG 26 aâé-¢ Colourphotos: The 1.500 mission of 3.(H)/Aufklƒ¤...
Aircraft books
Limited Availability
£17.80
-
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

Start LIF19
No Scale
Luftwaffe im Focus Edition No 19 aâé-¢ 50 pages, about 67 photos - thereof 8 in colour, 3 colour profiles, 1 coloured map, 1 coloured document, 4 coloured emblems, Readerforum aâé-¢ Yellow or orange the never-ending discussion: Colour slides provide new insight aâé-¢ Aircraft in Focus: The Junkers Ju-88 Flamethrower Aircraft of I./KG 51 aâé-¢ The Staffel Emblem of 3./JG52: One of the last unknown fighter emblems is identi...
Aircraft books
Limited Availability
£17.80
-
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

Start LIF23
No Scale
Luftwaffe im Focus Edition No.23 aâé-¢ 50 pages, 52 photos - thereof 7 in colour, 2 colour profiles, 1 coloured emblem, 3 coloured maps, Readerforum aâé-¢ Scenery: Hit in the Cockpit! (JG26) aâé-¢ Colour Photos of a Seldom-Seen Kampfgeschwader (I./KG 28) aâé-¢ Aircraft in Focus: An Extraordinary Aircraft from the Defense of the Reich The Fw-190 of Major Philipp, Geschwaderkommodore of JG1 aâé-¢ Unknown Emble...
Aircraft books
Limited Availability
£17.80
-
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

Start STARTDAS
Das vergessene As. Der Jagdfliger Gerhard Barkhorn by Bend Barbas The Forgotten Ace Fighter Pilot Gerhard Barkhorn Despite the fact that Gerhard Barkhorn scored a total of 301 victories while flying with Jagdgeschwader 52 on the Eastern Front, making him the second most successful fighter pilot of the Second World War after Erich Hartmann, today he is almost forgotten. This book describes Barkhorn's wartime experiences w...
Aircraft books
Limited Availability
£54.99
-
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

Start UBF06
U-Boot im Focus Edition No 6 aâé-¢ 50 pages, 58 photos - thereof 3 colour-photos, 3 colour-conning tower sideviews, 1 coloured emblem, 1 coulered document, Readerforum aâé-¢ U-Boat in focus: U-404 The "Viking Ship" on a Patrol with an Underwater Camera aâé-¢ Unusual: U-178 with Wheels! aâé-¢ Photos with a Story: The Forgotten Negatives The Only Photos of U-215 aâé-¢ Conning Tower: U-702 The Boat with the Elephant's...
Ship books
Limited Availability
£17.80
-
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

Start UBF09
No Scale
U-Boot im Focus Edition No 9 ' 56 pages, 54 photos - thereof 12 in colour, 1 coloured sideview (U-509 Type IX), 4 coloured conning tower sideviews (U-509), 3 coloured maps, 1 coloured Emblem, Readerforum ' Boat in Focus: The Operations of 'Sea Wolf' Boat U-509 (Typ IX) ' Pennants: Success against PQ-14 (U-403) ' Unknown Emblems: The 3rd Submarine Flotilla's First Emblem ' Unusual: Awarding the War Merit Cross to U-6...
Ship books
Limited Availability
£17.80
-
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

Start UBF12
No Scale
U-Boot im Focus Edition No 12 ƒ¯é¿é½ 50 pages with 46 photos - thereof 4 in colour, 2 coloured documents, 1 coloured conning tower sideview, 2 coloured boat-sideviews, 4 coloured maps, 4 coloured emblems and Readers Forum ƒ¯é¿é½ Conning Towers: The Cape Buffalo Head Emblem on U-10's Conning Tower ƒ¯é¿é½ Color Photos: Late-War Color Photos of the 21st Flotilla (U-733) ƒ¯é¿é½ Boat in Focus: U-249's FuMB ...
Ship books
Limited Availability
£17.80
-
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

Start UBF17
No Scale
U-Boot im Focus 17 50 pages with 51 photos - thereof 1 in colour, 3 coloured emblems, 1 coloured document, 2 coloured conning tower frontviews, 4 coloured maps, Readers Forum aâé-¢ Commanders: Kptlt. Friedrich Guggenberger, Kptlt. Helmut Rosenbaum,Kptlt. Heinrich Schonder, Kptlt. Fritz Frauenheim,Kptlt. Werner Kraus, Oblt.(Ing.) Erich Zƒ¼rn aâé-¢ Reader's Forum: The Spanish Cross for SubmarinersU-393 The Experimental Boat of ...
Ship books
Limited Availability
£17.80
-
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

Start STARCAMERA
No Scale
'Camera' At the Front with his camera, a war correspondent with I(J)/LG 2 and JG 77".
Aircraft books
Limited Availability
£59.99
-
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

Start STBAND1
Decals zum Blech. Embleme der Luftwaffe - Band 1 Junkers Ju-88; Messerschmitt Bf-109; Focke-Wulf Fw-190; Fw-189; Henschel Hs-126; Fieseler Storch Fi-156C;
Aircraft decals (military)
Limited Availability
£19.50
-
In Stock
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